{"id":1636,"date":"2017-08-03T02:55:02","date_gmt":"2017-08-03T02:55:02","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1636"},"modified":"2024-09-23T08:03:33","modified_gmt":"2024-09-23T08:03:33","slug":"parse-multi-dimensional-json-array-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/parse-multi-dimensional-json-array-ssis\/","title":{"rendered":"Parse JSON array in SSIS or ODBC Drivers"},"content":{"rendered":"

Introduction<\/h2>\n

In our previous post<\/a> we saw how to parse JSON arrays<\/strong>. Now let’s look at more advanced techniques to parse multi-dimensional JSON array in SSIS ( \u00a0e.g. 2D – JSON array inside array). We will use SSIS JSON Source<\/a> to parse complex nested JSON in few clicks. Tips and Tricks mentioned in this article also apply to ODBC PowerPack API Drivers<\/a>.<\/p>\n

2D arrays are used to stuff more data in most compact way. Since you don’t have to repeat column names you save great amount of space. 2D array JSON format is close to CSV format in data size but it gives advantage and structure of JSON without adding extra fat.<\/p>\n

Parsing JSON Arrays – Simple hierarchy<\/h2>\n

If you have simple JSON \/ XML Structure then you can use Select Filter option<\/strong> to parse array and flatten the hierarchy. You can select top level array or nested array to de-normalize the structure.<\/p>\n

For example you can select $.orders[*] to extract all order records or use $.orders[*].items[*] to get each order items for all orders. There is also possibility to use light weight expression (like WHERE clause). Check this article how to use JSONPath<\/a>\u00a0but it has its own limitation,<\/p>\n

\"Select<\/a>

Select Filter<\/p><\/div>\n

 <\/p>\n

Parsing Multi-Dimensional JSON Arrays<\/h2>\n

Now lets look at how to parse various multi-dimensional JSON array patterns in SSIS using SSIS JSON Source\u00a0<\/a>\u00a0or ODBC API drivers like JSON Driver<\/p>\n

    \n
  1. 2D Value Array with schema information (column names in a separate array) – Pattern 1<\/strong><\/li>\n
  2. 2D Value Array without schema information – Pattern 2<\/strong><\/li>\n
  3. Parse JSON Array using Complex Transformation – Pattern 3<\/strong><\/li>\n
  4. Key\/Value Transformation – Pattern 4<\/strong><\/li>\n
  5. Multiple columns using JSONPath Expression (Google Geocoding API Usecase) – Pattern 5<\/strong><\/li>\n
  6. Columnless Array – Pattern 6<\/strong><\/li>\n<\/ol>\n

    Pattern 1 – JSON Array using Simple Transformation (Column names found in JSON)<\/h3>\n

    This is the most common pattern of multi-dimensional JSON array where column names are stored in a separate array and values are stored in another 2D array<\/p>\n

    {\r\n  \"columns\" : [\"Id\", \"FirstName\", \"IsActive\"],\r\n  \"rows\" : [ [1,\"bob\",true], [2,\"sam\",false], [3,\"joe\",true] ]\r\n}<\/pre>\n
    \"\"<\/a><\/p>\n
    \"\"<\/a><\/p>\n
    \"\"<\/a><\/p>\n
    Pattern 2 – JSON Array using Simple Transformation\u00a0(Column names not found in JSON)<\/h3>\n
    This is another common pattern of multi-dimensional JSON array where there are no column names. Values are stored in 2D array. Google SpreadSheet API<\/a> uses this approach. In this pattern you don’t have column names specified so we have to enter manually (This like a CSV file without column names header). On the 2D array Tab check “Specify columns list manually” option. Then enter column names separated by comma. Enter same number of columns in same order as value array.<\/p>\n
    {\r\n  \"rows\" : [ [1,\"bob\",true], [2,\"sam\",false], [3,\"joe\",true] ]\r\n}<\/pre>\n
    \"\"<\/a><\/p>\n
    \"\"<\/a><\/p>\n
    \"\"<\/a><\/p>\n
    Pattern 3 – JSON Array using Complex Transformation<\/h3>\n
    Below is more complex version where column names are found in array of column schema documents. Values are also stored in 2D array with complex structure. Google BigQuery<\/a> uses similar complex structure<\/p>\n
    {\r\n  \"schema\" : { \r\n    \"columns\" : [\r\n      {\"Name\":\"Id\", \"Type\":\"int\"}, \r\n      {\"Name\":\"FirstName\", \"Type\":\"string\"}, \r\n      {\"Name\":\"IsActive\", \"Type\":\"bool\"}\r\n    ]\r\n  },\r\n  \"rows\" : [ \r\n    { \"rowid\":1 , \"values\" : [1,\"bob\",true] }, \r\n    { \"rowid\":2 , \"values\" : [2,\"sam\",false] },\r\n    { \"rowid\":3 , \"values\" : [3,\"joe\",true] }\r\n  ]\r\n}<\/pre>\n
    \"\"<\/a><\/p>\n
    \"\"<\/a><\/p>\n
    \"\"<\/a><\/p>\n
     <\/p>\n
    Here is another complex variation involving multiple JSON 2D arrays, wherein column names are present within an array of column schema documents. Additionally, values are stored in a 2D array with a sophisticated structure. The Google Analytics Data API<\/a> also employs a similar intricate structure for data representation.<\/p>\n
    {\r\n\t\"dimensionHeaders\": [{\r\n\t\t\"name\": \"transactionid\"\r\n\t}, {\r\n\t\t\"name\": \"date\"\r\n\t}, {\r\n\t\t\"name\": \"CampaignId\"\r\n\t}, {\r\n\t\t\"name\": \"CampaignName\"\r\n\t}, {\r\n\t\t\"name\": \"GoogleAdsAdGroupName\"\r\n\t}, {\r\n\t\t\"name\": \"googleAdsAdGroupId\"\r\n\t}, {\r\n\t\t\"name\": \"defaultChannelGroup\"\r\n\t}],\r\n\t\"metricHeaders\": [{\r\n\t\t\"name\": \"sessions\",\r\n\t\t\"type\": \"TYPE_INTEGER\"\r\n\t}],\r\n\t\"rows\": [{\r\n\t\t\t\"dimensionValues\": [{\r\n\t\t\t\t\"value\": \"(not set)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"20230709\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"(not set)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"(referral)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"(not set)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"Referral\"\r\n\t\t\t}],\r\n\t\t\t\"metricValues\": [{\r\n\t\t\t\t\"value\": \"5\"\r\n\t\t\t}]\r\n\t\t},\r\n\t\t{\r\n\t\t\t\"dimensionValues\": [{\r\n\t\t\t\t\"value\": \"(not set)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"20230711\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"(not set)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"(referral)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"(not set)\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"\"\r\n\t\t\t}, {\r\n\t\t\t\t\"value\": \"Referral\"\r\n\t\t\t}],\r\n\t\t\t\"metricValues\": [{\r\n\t\t\t\t\"value\": \"6\"\r\n\t\t\t}]\r\n\t\t}\r\n\t]\r\n}<\/pre>\n
    \"\"<\/a><\/p>\n
    In the Column name and Values Filter, instead of specifying individual arrays (e.g., dimensionHeaders and metricHeaders), we need to use the ‘*’ wildcard symbol to retrieve data from all arrays in a single output. This allows us to gather all the arrays’ data efficiently and conveniently.<\/p>\n
    \"\"<\/a><\/p>\n
    <\/h3>\n
    Pattern 4 – Key\/Value Transformation<\/h3>\n
    SSIS PowerPack v2.6.6 and later introduced new transformation type called Key\/Value transformation. This option Pivots dynamic Key\/Value pairs of some attributes into Columns. Zoho CRM is best use case. Fully described here<\/a>.<\/p>\n
    {\r\n  \"rows\": [\r\n    {\r\n      \"rownum\": 1,\r\n      \"fields\": [\r\n        {\r\n          \"name\": \"FirstName\",\r\n          \"value\": \"Bob\"\r\n        },\r\n        {\r\n          \"name\": \"LastName\",\r\n          \"value\": \"Tylor\"\r\n        },\r\n        {\r\n          \"name\": \"Phone\",\r\n          \"value\": \"111-111-1111\"\r\n        }\r\n      ]\r\n    },\r\n    {\r\n      \"rownum\": 2,\r\n      \"fields\": [\r\n        {\r\n          \"name\": \"FirstName\",\r\n          \"value\": \"Sam\"\r\n        },\r\n        {\r\n          \"name\": \"LastName\",\r\n          \"value\": \"Smith\"\r\n        },\r\n        {\r\n          \"name\": \"Phone\",\r\n          \"value\": \"222-222-2222\"\r\n        }\r\n      ]\r\n    }\r\n  ]\r\n}<\/pre>\n
    To transform above JSON where fields appear as column (e..g FirstName, LastName and Email) you need to apply following settings on JSON Source UI.<\/p>\n
      \n
    1. Select Filter $.rows[*]<\/strong><\/li>\n
    2. Goto 2D Array Tab and select following settings\n
        \n
      1. Transform Type = Key\/Value<\/li>\n
      2. Column Name Filter =\u00a0\u00a0$.fields[*].name<\/strong><\/li>\n
      3. Row Value Filter =\u00a0\u00a0$.fields[*].value<\/strong><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
        \"\"<\/a><\/p>\n
        Pattern 4.1 – Rootless JSON Array Key\/Value Transformation<\/h4>\n
        Sometimes you might get a rootless JSON array is a JSON structure where the top level is an array, not an object. This format is common when representing collections like lists of items or Custom Columns. Parsing it requires direct access to the array and its elements without a surrounding parent object.<\/p>\n
        [\r\n    {\r\n        \"name\": \"FirstName\",\r\n        \"value\": \"Bob\"\r\n    },\r\n    {\r\n        \"name\": \"LastName\",\r\n        \"value\": \"Tylor\"\r\n    },\r\n    {\r\n        \"name\": \"Phone\",\r\n        \"value\": \"111-111-1111\"\r\n    }\r\n]<\/pre>\n
        Output:<\/strong>
        \nFrom this JSON array’s element name should be used as the column header, and the corresponding element value should populate the relevant column.<\/p>\n
        FirstName  |  LasteName  |  Phone\r\nBob        |  Tylor      |  111-111-1111<\/pre>\n
         
        \nTo parse this type of rootless JSON array, we need to use the “EnableSingleRowOutput<\/strong>” and “RawOutputDataTemplate<\/strong>” settings. These settings ensure that the array is treated as individual rows of data and that the raw JSON structure is maintained during parsing, allowing for direct access to each element within the array.
        \n 
        \nTo do so Right-click on the         JSON Source<\/a> or JSON Parser Transform<\/a>, go to “Properties<\/strong>”<\/p>\n