Parse JSON Column containing multiple arrays and keys

Question

I've gone through a few examples/tutorials trying to parse JSON columns containing multiple arrays using the JSON functions within SQL Server (2017), however I'm still coming up short as far as trying to parse the following JSON format as shown below.

For example purposes - I'll declare this into a @json variable in the code sample below. I've tried multiple iterations to try and extract this values, so this where I left off when trying to parse the example shown below.

DECLARE @json nvarchar(max)
SET @json = N'[
  [
    {
      "Title": "Title 1",
      "Property": "String1",
      "Value": "123456",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 2",
      "Property": "String2",
      "Value": "54321",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ],
  [
    {
      "Title": "Title 3",
      "Property": "String3",
      "Value": "33333333",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 4",
      "Property": "String4",
      "Value": "44444444",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ]
]'
SELECT JSON_VALUE(j.value, '$.Title') AS 'Title Output', j.[key], j.Value, JSON_VALUE(j.value, '$.Title[0]') AS Title1
--STRING_AGG('Value: ' + v.[value] + ' Title: ' + t.[value], ', ') AS [Values]
FROM OPENJSON(@json) j

With how the JSON format is being stored into the column, I'm hoping to obtain all Title's and Values as shown below.

Expected Output Column

Title 1 - 123456, Title 2 - 54321, Title 3 - 33333333, Title 4 - 44444444

I also tried altering the JSON string before parsing the data as well such as pre-pending with an array name in order to help identify the placement for the collection members, however I was only able to obtain the first Title & Value.

LukStorms · Accepted Answer · 2019-12-19 23:35:42Z

It contains an array of arrays [] with objects {}

You can CROSS APPLY the values of an array to get to the elements.

Here's an example that uses a table.
But it's the same principle for a variable.

CREATE TABLE test
(
  id int identity(1,1) primary key,
  jsonCol nvarchar(max)
);

INSERT INTO test VALUES
(N'[
  [
    {
      "Title": "Title 1",
      "Property": "String1",
      "Value": "123456",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 2",
      "Property": "String2",
      "Value": "54321",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ],
  [
    {
      "Title": "Title 3",
      "Property": "String3",
      "Value": "33333333",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 4",
      "Property": "String4",
      "Value": "44444444",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ]
]');

Query:

SELECT id, a.[Values]
FROM test t
OUTER APPLY
(
   SELECT
   STRING_AGG(CONCAT(
      JSON_VALUE(obj.value,'$.Title'),
      ' - ', 
      JSON_VALUE(obj.value,'$.Value')
   ), ', ') AS [Values]
   FROM OPENJSON(t.jsonCol) AS arr
   CROSS APPLY OPENJSON(arr.Value) AS obj
) a;

Result:

id | Values                                                                   
-: | :------------------------------------------------------------------------
 1 | Title 1 - 123456, Title 2 - 54321, Title 3 - 33333333, Title 4 - 44444444

Test on db<>fiddle here

Collectives™ on Stack Overflow

Parse JSON Column containing multiple arrays and keys

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related