0
Return JSON
Hello, I have this JSON data that is returned from a SQL select statement:
{"result":{"recordsets":[[{"status":"Enabled"}]],"recordset":[{"status":"Enabled"}],"output":{},"rowsAffected":[1]}}
How do I return the value of
status
?
3 replies
-
If you want the first status, this works: (in this example, the data is held in a text field "JSON" and the code is written in a formula field)
let i := parseJSON(JSON); let j := first(i.result.recordsets); item(j, 0).status
-
More simplyfied:
>for the first status :
let i := parseJSON(JSON);
first(first(i.result.recordsets)).status>for the second status:
let i := parseJSON(JSON);
first(i.result.recordset).status -
,
Another technique we use with nested json arrays is nested item() functions.
Using your example:
--- task... find the value of each "status" embedded in the following json string nested item functions are handy for obtaining json values 'Data Log' is a multiline text field for viewing output ---; 'Data Log' := null; let tLog := ""; let tJsonString := "{ ""result"": { ""recordsets"": [ [{ ""status"": ""Enabled"" }] ], ""recordset"": [{ ""status"": ""Enabled"" }], ""output"": {}, ""rowsAffected"": [1] } }"; let tJson := parseJSON(tJsonString); tLog := formatJSON(tJson) + " "; let tRecordsetsStatus := item(item(item(tJson.result, "recordsets"), 0), 0).status; --- zeros represent the first value in each sub-array (tip: count the occurences of opening square brackets. After element recordsets there are 2) ---; tLog := tLog + "First Status is " + tRecordsetsStatus + " "; let tRecordsetStatus := item(item(tJson.result, "recordset"), 0).status; --- zeros represent the first value in each sub-array (tip: count the occurences of opening square brackets. After element recordset there is 1) ---; tLog := tLog + "Second Status is " + tRecordsetStatus; 'Data Log' := tLog;
We find this technique more closely mimics the json itself and allows you to more easily remember data structures.
I hope that helps.
Kind regards, Andrew
Content aside
- 6 mths agoLast active
- 3Replies
- 84Views
-
3
Following