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?

  • 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.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟣
    edited April 8

    I blogged about it in YouTube / on my blog.


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

  • 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.

  • 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

  • Ok, sounds good. Thank you both.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!