SQL JSON_VALUE / JSON_QUERY From Array and transposed to Row

user_9007560 0
Viewed 1327

I want to extract Information from an Array inside a JSON string in a Microsoft SQL Server database.

If I have a JSON object like this:

CREATE TABLE myTable([Id] int, [JsonInfo] varchar(max));

INSERT INTO myTable  ([Id], [JsonInfo])
VALUES (1, 
    '{
      "$id": "1",
      "Id": "32766177-18c7-4c2d-bbb5-02588a73ff72",
      "Metadata": [
        {
          "Identifier": "Identifier1",
          "Value": "aaa"
        },
        {
          "Identifier": "Identifier2",
          "Value": "bbb"
        },
        {
          "Identifier": "Identifier3",
          "Value": "ccc"
        },
      ],
    }'
);

The only way I found to access the entire Array was:

SELECT Id, value AS Metadata
FROM myTable t
CROSS APPLY OPENJSON( JSON_QUERY(t.JsonInfo, '$.Metadata'))

To access the information in the array, I did the following:

SELECT 
    JSON_VALUE(Metadata, '$.Identifier') AS Identifier,
    JSON_VALUE(Metadata, '$.Value') AS Value
FROM
    (SELECT Id, value AS Metadata
     FROM myTable t
     CROSS APPLY OPENJSON( JSON_QUERY(t.JsonInfo, '$.Metadata'))
    );

My result is:

Identifier  | Value
------------+--------
Identifier1 | aaa
Identifier2 | bbb
Identifier3 | ccc

The result I should be:

Identifier1 | Identifier2 | Identifier3
------------+-------------+------------
aaa         | bbb         | ccc

The result with non matching records:

Identifier1 | Identifier2 | Identifier3 |Identifier4
------------+-------------+-------------+-----------
aaa         | bbb         | ccc         | NULL
aaa         | bbb         | NULL        | ddd

I know I could transpose it with PIVOT but it seems too complex for this scenario.

Any suggestions on how I could achieve this easily?

sql-server arrays sql json
1 Answers

Note: Your supplied JSON is not valid. I had to modify it for my example. If your JSON sample is how your production data looks, you have other issues that need addressing.

The simplest approach would be to do a PIVOT on your data, like so:

DECLARE @myTable TABLE ( [Id] INT, [JsonInfo] VARCHAR(MAX) );

INSERT INTO @myTable ( [Id], [JsonInfo] )
VALUES (1, 
    '{
      "$id": "1",
      "Id": "32766177-18c7-4c2d-bbb5-02588a73ff72",
      "Metadata": [
        {
          "Identifier": "Identifier1",
          "Value": "aaa"
        },
        {
          "Identifier": "Identifier2",
          "Value": "bbb"
        },
        {
          "Identifier": "Identifier3",
          "Value": "ccc"
        },
        {
          "Identifier": "Identifier4",
          "Value": ""
        }
      ]
    }'
);

SELECT
    Id, md.*
FROM @myTable AS mt
OUTER APPLY (

    SELECT * FROM (

        SELECT Identifier, [Value] FROM OPENJSON ( JsonInfo, '$.Metadata' ) WITH (
            Identifier VARCHAR(50) '$.Identifier',
            [Value] VARCHAR(50) '$.Value'
        )

    ) AS ids
    PIVOT (
        MAX ( [Value] ) FOR Identifier IN ( [Identifier1], [Identifier2], [Identifier3], [Identifier4] )
    ) AS pvt

) AS md;

Returns

+----+-------------+-------------+-------------+-------------+
| Id | Identifier1 | Identifier2 | Identifier3 | Identifier4 |
+----+-------------+-------------+-------------+-------------+
|  1 | aaa         | bbb         | ccc         |             |
+----+-------------+-------------+-------------+-------------+

This approach only works if you know the expected value and number of rows for each Identifier and I doubt that's the case as you already mentioned PIVOT. As a result, you'll be forced to use a dynamic SQL approach.

I don't fully understand your need as I'm not sure where the mismatched data is coming from the JSON in your example, however, continuing with the same setup from above, you could try the following:

-- Declare a variable to hold the dynamic SQL statement.
DECLARE @sql VARCHAR(MAX) = ''; -- must be initialized as an empty string.

-- Using the SQL hack to append continuous rows to a variable, we can build a list
-- of column names and their values from your JSON.
SELECT
    @sql = @sql + ', NULLIF ( ''' + md.[Value] + ''', '''' ) AS [' + md.Identifier + ']'
FROM @myTable AS mt
OUTER APPLY (

    SELECT 
        Identifier, [Value] 
    FROM OPENJSON ( JsonInfo, '$.Metadata' ) WITH (
        Identifier VARCHAR(50) '$.Identifier',
        [Value] VARCHAR(50) '$.Value'
    ) AS ids

) AS md
WHERE mt.Id = 1; -- <= note the record restriction.

-- Complete the dynamic SQL statement by adding SELECT and trimming
-- off the prefixed ", " from the column/value list by using STUFF.
SET @sql = 'SELECT ' + STUFF ( @sql, 1, 2, '' ) + ';';

-- Execute the dynamic statement.
EXEC ( @sql );

Returns

+-------------+-------------+-------------+-------------+
| Identifier1 | Identifier2 | Identifier3 | Identifier4 |
+-------------+-------------+-------------+-------------+
| aaa         | bbb         | ccc         | NULL        |
+-------------+-------------+-------------+-------------+

If you were to PRINT the completed @sql variable you would see:

SELECT NULLIF ( 'aaa', '' ) AS [Identifier1], NULLIF ( 'bbb', '' ) AS [Identifier2], NULLIF ( 'ccc', '' ) AS [Identifier3], NULLIF ( '', '' ) AS [Identifier4];

Hopefully, this helps get you on your way.

user_33913340
Related