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

null
    • Ninox partner
    • RoSoft_Steven.1
    • 7 mths ago
    • Reported - view

    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

    • Ninox partner
    • RoSoft_Steven.1
    • 7 mths ago
    • Reported - view

    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

    • Andrew_Dickey
    • 6 mths ago
    • Reported - view

      ,

    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