JSON - Working with Nested Arrays and Collections

[Maxime_Warnault]

My goal is to upload this JSON data from Apple Health into AirTable every day

Here’s my OUTPUT: output.json (5.9 KB)

But I don’t have access to the qty

In AirTable, I will have different columns like this:

From this data sample, for example, I want to have:

• 1220.075000000023 go into Active Energy (kcal) using the name active_energy as a filter.
• 805.795847750865 go into Dietary Water (L) using the name “dietary_water” as a filter.
• 10003 go into Step Count using the name “step_count” as a filter.

Here’s the blueprint I have so far if that helps:
fullblueprint.json (36.9 KB)

Thank you for your help
Max

[alex.newpath]

@Maxime_Warnault

• 1220.075000000023 go into Active Energy (kcal) using the name active_energy as a filter.
• 805.795847750865 go into Dietary Water (L) using the name “dietary_water” as a filter.
• 10003 go into Step Count using the name “step_count” as a filter.

Hi Max,

The missing element of what you’re trying to do is effective use of the map() function to filter on a name/value pair and extract a nested array.

The main issue with this is you need the proper key to extract and that must be described in dot notation. Since the elements you are looking for are in a nested array called data[] you need to type out the proper key to extract, the key that you wish to filter on in the main metrics array, and the value you wish to filter on:

Here’s the expression you can paste into the proper place in your airtable module:

{{map(1.data.metrics; "data[].qty"; "name"; "active_energy")}}

You’ll note that the second and third arguments of the map function describe the part of the strucure relative to the 1.data.metrics array described in the first argument. And these arguments are “raw” references to the datastructures. The last (fourth) argument to the map() function is the value of the key (ie name) you wish to filter on.

The important thing to note is you can click to add the 1.data.metrics part from the interface builder, but the other 3 arguments you MUST type out the raw value by hand. There is no way to click in the interface to select these parts of the structure from the “star” part of the UI. Or you can just copy and paste the expression above adjusting the first argument to fit.

This mechanism is described in the help file on mapping arrays. Unfortunately in this document they don’t describe what happens if the key you wish to extract the value for in the second argument is inside an array – thus the raw name data[].qty is how you grab the value out of the qty key in the data[] array.

I am attaching the blueprint I used to model your structure in a Parse JSON and the set variables module used to extract each of the qty values from the data[] array.

blueprint (17).json (11.2 KB)

[Maxime_Warnault]

@alex.newpath, thank you so much

It worked and I could finish the integration yesterday

Do you know if there’s a way to do this dynamically?

Meaning, instead of manually typing “active_energy” for example it would get the name as the key and assign qty as the value.

Thanks again

[alex.newpath]

@Maxime_Warnault

Do you know if there’s a way to do this dynamically?

Meaning, instead of manually typing “active_energy” for example it would get the name as the key and assign qty as the value.

That’s a really good question. Why not try it by setting the last argument to a variable and set it elsewhere? I have never used a variable for the fourth argument. Don’t see why it should not work.