Extract Items from a list

I have a mongodb data set that has a list of items nested within a field. I am trying to check if certain items are contained within that list, preferably in SQL. Is there a good way to do this in domo, such as a SPLIT or explode() function?

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    Hi @banderson

    Are the items you're checking against this list the same values all the time or would they vary depending on another dataset?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • The values should follow the same structure as this:     

    {

          "enterpriseId" : ObjectId("5cfdaf8f591f243a00c5d6f3"),

          "permissions" : [ 

            "admin"

          ]

        },

        {

          "enterpriseId" : ObjectId("5cfdaf8f591f243a00c5d6f3"),

          "permissions" : [ 

            "admin"

          ]

        }

    Where there can be any number of them, but each item should contain a enterprise id and permissions.

  • Is there a way to do mongo queries within domo?

  • GrantSmith
    GrantSmith Indiana 🥷

    The version of MySQL that Domo uses (5.6) doesn't support quite a few nice features like regular expressions so you'd need to resort to something like the LIKE command as the simplest option to see if a substring exists in your field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴
    edited April 2021

    I blogged about it in YouTube / on my blog.


    Side note, you can do the same thing in Magic 2.0

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thanks jaeW_at_Onyx,

    Do you know if something similar can be done when it is a list of json elements like that? My problem is all I see is that it is a List, where all elements are labeled that way. When I try to extract a specific piece, it still just shows a list. For example, the last column shows the extraction from the enterprises column that is also a list.


  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴

    it looks like the data wasn't imported properly. if the actual data contains "List" then you need to revisit your connector. Had the dataset imported properly, i would expect to see a lonnnnnnnnng string.


    P.S. may need to confirm with domo that you can store wide text string datasets.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • GrantSmith
    GrantSmith Indiana 🥷

    @jaeW_at_Onyx @banderson

    FYI There's a limit of 1024 characters for columns in your datasets.

    The text in one of my data columns is getting truncated

    Domo has a 1024-character limit. Any additional characters after this will be truncated. You can get around this by splitting the text between two columns to make sure it stays within the limit. You must do this in your own data before bringing it into Domo.

    https://knowledge.domo.com/Welcome/Getting_Started/07Troubleshooting#:~:text=Domo%20has%20a%201024%2Dcharacter%20limit

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Ok, sounds good. Thank you both.