Extract Items from a list

banderson

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?

GrantSmith

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?

banderson

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.

banderson

Is there a way to do mongo queries within domo?

GrantSmith

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

I blogged about it in YouTube / on my blog.

https://www.onyxreporting.com/blog/domo-parsing-json-in-a-mysql-56-dataflow

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

banderson

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

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

@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

banderson

Ok, sounds good. Thank you both.