Given the below JSON data and table that holds the data, how can I query the values and write to a new table with rows and columns split?

Basic table that contains the JSON:

CREATE TABLE BI.DataTable
(
    JsonDataText VARCHAR(MAX)
);

JSON data:

   {
       "datatable": {
          "data": [
             [
                "ZSFH",
                "99999",
                "2022-08-31",
                571106
             ],
             [
                "ZSFH",
                "99999",
                "2022-07-31",
                578530
             ],
             [
                "ZSFH",
                "99999",
                "2022-06-30",
                582233
             ],
             [
                "ZSFH",
                "99999",
                "2022-05-31",
                581718
             ]
          ]
       }
    }

When I use the JSON_VALUE function, I get a null for each column.

    SELECT 
      JSON_VALUE (JsonDataText, '$.data[0]') AS MetricCode,
      JSON_VALUE (JsonDataText, '$.data[1]') AS RegionID,
      JSON_VALUE (JsonDataText, '$.data[2]') AS ReportingPeriod,
      JSON_VALUE (JsonDataText, '$.data[3]') AS MetricValue
    FROM BI.DataTable
    WHERE ISJSON(JsonDataText) > 0

CodePudding user response：

Using OPENJSON() with the appropriate columns definitions and an additioinal APPLY operator is a possible approach:

SELECT j.*
FROM DataTable d
OUTER APPLY OPENJSON(d.JsonDataText, '$.datatable.data') WITH (
   MetricCode varchar(4) '$[0]',
   RegionID varchar(5) '$[1]',
   ReportingPeriod varchar(10) '$[2]',
   MetricValue int '$[3]'
) j
WHERE ISJSON(d.JsonDataText) = 1

Result:

Note, that the reasons for the NULL values returned from your current statement are:

• JSON_VALUE() extracts a scalar value from a JSON string, but the $.datatable.data part of the stored JSON is a JSON array with JSON arrays as items, so in this situation you need to use JSON_QUERY().
• The $.data[0] path expression is wrong.

The following example (using JSON_QUERY() and the correct path) extracts an item from the $.datatable.data JSON array:

SELECT 
   JSON_QUERY(JsonDataText, '$.datatable.data[0]') AS MetricCode,
   JSON_QUERY(JsonDataText, '$.datatable.data[1]') AS RegionID,
   JSON_QUERY(JsonDataText, '$.datatable.data[2]') AS ReportingPeriod,
   JSON_QUERY(JsonDataText, '$.datatable.data[3]') AS MetricValue
FROM DataTable
WHERE ISJSON(JsonDataText) > 0