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

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

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