Parsing a JSON from resulting LOOKUP()

Hi, How do I surface a table with only a specific (in this case, “species”)column from a JSON that is a result of a LOOKUP Query. I thought of nesting it into a FLATTEN() function but I don’t know how to implement it.

I also thought of parsing the JSON using a “Formula” type column but the formulas were not updating on the Sheets unless I click Save and Validate


We have a function called - STRINGTOJSON() which parses the resultant JSON from a LOOKUP JSON column. You can use this function in Datastore and you will be able to get the desired key.

Please note, for such a setup, you will also have to use a special function called as MAPKEYS() to define the key names. Using this you will be able to get the datastore suggestions in other controls

All you need to do is -

MAPKEYS(STRINGTOJSON(LOOKUP([regen_cover_crop.covercropgroup],regen_cover_crop.plan_name="Clover Barley Plan")),"speciesfamily:speciesfamily,species:species,plantingratelbsac:plantingratelbsac")

Also please ensure, the result of the LOOKUP is 1 record/row that can be passed to STRINGTOJSON().

Hope this helps.

@chirag1 I tried putting the formula you gave (+making sure the lookup result is 1 row) to a list variable on a PDF (in Drona PDFCreator). The List Variable is inside a dynamic table but I could not get it to show up on the pdf.


You will have to bind the formula ( MAPKEYS(STRINGTOJSON(LOOKUP())) ) in a datastore and then use the datastore’s reference to an input list type control, for instance - multiselect. Map the multiselect control to List Variable of the PDF task to show it on PDF.

Hope this helps.