I know this has been discussed elsewhere in the forum, but I'm having trouble turning data from JSON into variables. Thanks to the folks at How to parse a JSON content - #6 by martin I feel like I'm on the right path but missing something crucial.
I've curled some Airtable data into a variable called ATLookup, which looks like this:
{
"records" : [
{
"createdTime" : "2022-01-26T18:38:25.000Z",
"fields" : {
"Age" : "5",
"Email" : “customer27394@gmail.com",
"Parent First Name" : "Sarah",
"Parent Last Name" : “Smith”,
"Phone" : “(123) 555-5555”,
"Student First Name" : “Junior”,
"Student Last Name" : “Smith”
},
"id" : “rec728dlxx9
}
]
}
I'm trying to extract the 'Age' data (i.e. just the number '5'). I've tried the following to no avail:
Any advice on how to get this working?
Perhaps that problem is on the supply-side (JSON glitches), rather than the demand side (%JSONValue%) ?
- A number of the double quote characters in your JSON source are not the
" used by JSON, but "smart quote" characters, which can't be parsed as JSON.
- There doesn't appear to be any closing quote at all at the end of the
id string
i.e, that string is not yet JSON, but could become JSON with a few fixes.
Ah, I think those must be errors on my end, my apologies - I redacted some of the info for privacy and think I got a little sloppy. Here's the JSON result as it appears from curl (and prettified) - still not working with the %JSONValue% stuff, though:
{
"records" : [
{
"createdTime" : "2022-01-26T18:38:25.000Z",
"fields" : {
"Age" : "5",
"Email" : "customer_goes_here@gmail.com",
"Parent First Name" : "Sarah",
"Parent Last Name" : "Smith",
"Phone" : "(123) 555-5555",
"Student First Name" : "Joe",
"Student Last Name" : "Smith"
},
"id" : "recDu2872d"
}
]
}
In addition, you don't yet have the full path to the Age key.
Assuming that we re-rewrite your source, supplying JSON double quotes wherever they are needed:
%JSONValue%ATlookup.records[1].fields.Age%
(Notice that JSONValue index references to Array elements are 1-based – position 0 is reserved, and references an integer length value for the array)
JSON value path.kmmacros (2.9 KB)
2 Likes
Ah, that did it - thanks so much! I didn't know about using [1] at all.
The records key is pointing to an Array there – not directly to the single object which it contains.
3 Likes
