How to access multiple elements in a JSON with PARSE()

Ask the Community
#1

Love the parse functionality. Quick question regarding Lists.

Given the following JSON I want to get all names in the items list.
{items:[{name:"foo", size:"foo_size"}, {name:"bar", size:"bar_size"}]}

Which Path do I need to use to get to something comparable to
foo, bar
as an output?

(Imagined it to be items.name without referencing any [x], did not work)

Thanks a lot!

1 Like
#2

Hi @marc!

You can select the rows in a JSON by using [square brackets].

As in example, if your JSON is in cell A1, you can parse foo with the formula
PARSE(A1,ā€œitems.[0].nameā€)
and bar with
PARSE(A1,ā€œitems.[1].nameā€).

If you have any further questions, just let us know.

#3

Hi @Torben,

thanks for the quick answer! I would like to make this without concatenating PARSE(A1,ā€œitems.[0].nameā€) &","& PARSE(A1,ā€œitems.[1].nameā€), since I do not know how many items will be in this list.

Is there a way to solve this?

Thanks
Marc

#4

Hello @marc.

Yes, there is a way! We support the full JSONPath notation, meaning that you can iterate across an array.

  • =PARSE(A1,"items.[*].name") should return what you want. * stands for ā€œallā€.

You can do some powerful stuff. The full spec of the regular expressions accepted in parse is here.

Can you please try?

It would also be interesting to know what youā€™d like to do next with this list of values.

1 Like
#5

Thanks for the quick response @humberto, it now works perfectly. I classify items in this step and filter by the attributes attached to every item afterwards.

#6

@marc great. will mark topic as solved. You can also do it by ticking the checkmark.

Interested to see the outcome.

#7

Hey @humberto

This works well, but how can I take it one step further, to merge the values in the array, like in @marcā€™s original question?

I tried this =CONCATENATE(PARSE(B2,"['root'].['higher'].[*].['lower']"))
but it merges JSON plain text, e.g. ["Jane","John"]

Obrigado!

#8

Hey @gabriel :wave:t4:

Have you tried using SUBSTITUTE?

Hereā€™s what I was able to do -

Input (A1): {"items":[{"name":"foo", "size":"foo_size"}, {"name":"bar", "size":"bar_size"}]}
Output (A2): =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PARSE(A1,"['items'][*].['name']"),'"',""),"[",""),"]","")
Result (A2): foo,bar

We may have to play around with PARSE more as it would depend on the kind of JSON structure youā€™re working with but I hope this gets the job done :slightly_smiling_face:

Let me know what you think!

#9

Hi @sourabh
That was my last resort option, but it will do.
Thanks!

#10

Bam! Split is here :metal:

3 Likes