Help Desk

Submit a ticket My Tickets
Welcome
Login

Parsing Nested JSON Arrays

To parse a nested JSON array, the JSON BizApp Array to CSV Command must be used.  Online resources, such as jsonpathfinder.com can be used to help determine the path to the nested array elements.

In this sample JSON, we denote the objects and arrays for reference.  In JSON, arrays are denoted by square brackets, and objects are denoted by curly brackets.

There are multiple parameters that need to be specified in the Command configuration to parse the data from the nested array.

  1. The Path to root parameter is the base level node of the JSON array.  In this example data, RootNode should be specified for the path to root parameter.

  2. The individual data points that need to be extracted are specified in the Columns section of the Command.  In this example data, the Date, Amount, and ManuallyAdjusted object values are extracted from each Values array.  The JSONPath parameter is a continuation of the Path to root parameter.  The use of the asterisks in the JSONPath accounts for the RootNode array having multiple objects in the Values array. 

  3. The Multi-value Delimiter parameter is used to specify the delimiter for a given column when more than one value is extracted based on the JSONPath.  In this example data, since the Values array has multiple elements, we use the comma to separate the field values extracted from the array.

  4. The delimiter between each column of the data set is set to Tab.  In this example, this is the character that separates the Date, Amount, and Adjusted columns.

Currently, the Array to CSV Command produces a format that is technically correct but likely needs some post-processing to be functionally valuable.  For this example data, the conversion produces three columns delimited by a tab - Date (yellow), Amount (green), and Adjusted (blue).  Within each column, there are multiple values, each of which is delimited by a comma.

As of the publishing of this article (June 2020), an enhancement is planned to the Array to CSV Command that will perform the following steps natively.  Until the Command is enhanced, the following provides a solution to transform the data set.

To transform this data set, we leverage the Advanced Query Command from the Tabular Transformation BizApp.  

  1. After adding the Advanced Query Command to the Chain, the output of the Array to CSV Command is defined as the table data (splitter) on which the query will be executed.

  2. A recursive SQL query is created to transform the technical output

  3. The delimiter of the data is changed from Tab to Comma

Let’s dissect the SQL query.

  1. All the data generated by the Array to CSV Command is queried.  Each field that needs to be split is specified.  In this example, Date, Amount, Adjusted.  Field names can be added or removed as need as long as the pair (denoted by yellow) is maintained.

  2. A Query that will parse the multiple field values is combined (Union All) with the prior query.  The values of all_dates, all_amounts, and all_adjusted represent the multiple values for the Date, Amount, and Adjusted fields.  The all_ fields can be expanded or reduced depending on functional requirements but the all_ field name must match the value used in the recursive action (Step 3 below).  Also note, the substring functions are in pairs for each field.  If modifying the number of fields, be sure to do so in pairs. 

  3. A temporary table is recursively populated with data from steps 1 and 2.  Essentially, each field value is loaded into its own unique row.  This is done for each value in each field.  Each fields to be populated in the temporary table must be specified in pairs such as date (individual value) and all_dates (recursively parsed multi-values). 

  4. Finally, the result of the recursive parsing of each value across each field is returned in a more functional table layout.  Notice that the individual value fields (date, amount, adjusted) are retrieved.

The result is the following:

The SQL used in this solution, as well as a sample JSON dataset, is below for reference.

We hope you find this article useful and encourage you to post any questions or comments that you may have.  If you need additional support, please feel free to email us at support@onecloud.io

WITH RECURSIVE expanded(
    date, all_dates, amount, all_amounts, adjusted, all_adjusted
) AS (

    SELECT
        ''
        , "Date" || ','
        , ''
        , "Amount" || ','
        ,''
        , "Adjusted" || ','
    FROM splitter
    WHERE "Amount" 
    
    UNION ALL
    
    SELECT 
        SUBSTR(all_dates, 0, INSTR(all_dates, ','))
        , SUBSTR(all_dates, INSTR(all_dates, ',')+1)
        , SUBSTR(all_amounts, 0, INSTR(all_amounts, ','))
        , SUBSTR(all_amounts, INSTR(all_amounts, ',')+1)
        , SUBSTR(all_adjusted, 0, INSTR(all_adjusted, ','))
        , SUBSTR(all_adjusted, INSTR(all_adjusted, ',')+1)
    FROM expanded
    WHERE all_amounts <> ''
)

-- SELECT from the recursive table ----------------------
SELECT 
    date, amount, adjusted
FROM expanded
WHERE amount <> ''
ORDER BY 
    date ASC
    , amount DESC
    ,adjusted DESC
;  


{
  "RootNode": [
    {
      "Values": [
        {
          "Amount": 5.0679,
          "Date": "2003-01-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        },
        {
          "Amount": 6.0382,
          "Date": "2003-02-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        },
        {
          "Amount": 0.5737,
          "Date": "2003-03-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        },
        {
          "Amount": -1.5139,
          "Date": "2003-04-01",
          "Annotation": null,
          "ManuallyAdjusted": true,
          "IsForecasted": null
        },
        {
          "Amount": 0.6911,
          "Date": "2003-05-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        }
      ]
    },
    {
      "Values": [
        {
          "Amount": 15.0679,
          "Date": "2013-01-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        },
        {
          "Amount": 16.0382,
          "Date": "2013-02-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        },
        {
          "Amount": 10.5737,
          "Date": "2013-03-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        },
        {
          "Amount": -11.5139,
          "Date": "2013-04-01",
          "Annotation": null,
          "ManuallyAdjusted": false,
          "IsForecasted": null
        },
        {
          "Amount": 10.6911,
          "Date": "2013-05-01",
          "Annotation": null,
          "ManuallyAdjusted": true,
          "IsForecasted": null
        }
      ]
    }
  ]
}


Login to post a comment