Read an array of JSON object using coda formula

[Zainab_Fitian]

I have a Text which is invoking changes in the board. If there’s only one change, I extract a simple JSON object. If there are multiple changes, I extract an array of simple JSON objects.
I use FormulaMap(CurrentValue.ParseJSON(“$.MyKey”)) to extract the value of each key.

The problem is it only reads the values when I have only one simple JSON object, when I have an array of objects it doesn’t work.

This is the JSON I have :
{

“company_name”: “The company name”,

“name”: “the name”,

“gender”: “Male”,

“age”: “Not specified”,

“nationality”: “Italian”,

“function_type”: “CEO”,

“event”: “New CEO appointment”,

“event_date”: “Not specified”

}

[Math_24]

Hi, use :

[Zainab_Fitian]

It gives the same result as the syntax that I am using. It reads one JSON object but not an array

[Math_24]

What you’re showing is a json, not a json with table.
If one of the keys is an array, then parsejson on the key is looped over.

[Zainab_Fitian]

Sorry for the confusion, I showed the simple Json object with which the formula works perfectly. But when the input is an array like this {

[

{

  "company_name": "Comp",

  "name": "JB",

  "gender": "Male",

  "age": "> 25 years",

  "nationality": null,

  "function_type": "Chief Financial Officer (CFO)",

  "event": "Appointment",

  "event_date": "19 June 2024"

},

{

  "company_name": "Comp",

  "name": "Ps",

  "gender": null,

  "age": null,

  "nationality": null,

  "function_type": "Transition to Group Head of Mergers & Acquisitions",

  "event": "Transition",

  "event_date": "19 June 2024"

}

]

The formula doesn’t work

[Felipe_Arnhold1]

Hello @Zainab_Fitian

I think the problem is that coda does not now that is an array of objects.
If you are trying to do a FormulaMap or Foreach using a string with “[ ]” delimiters, the coda formula consider one element and the parseJSON formula also does not expect an array.
To make it work, you need to convert the string into an array and then use formulaMap and parseJSON. I think you cannot do it easily with built in formulas.

I made a simple pack that do this for you. It just parse the input string and return an array of objects (link to the pack). It uses the JSON.parse() javascript function.

With this you can use something like:
str2array(‘[{“id”: 123, “name”: “TEST”},{“id”: 456, “name”: “TEST2”}]’).ForEach(
currentValue.parseJSON(“$.name”)
)

This will return a list equal to (“TEST”, “TEST2”)

Let me know if this works for you.

Best Regards
Felipe Arnhold