SQL JSON_VALUE / JSON_QUERY From Array and transposed to Row

Question

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?

Your statement has syntax errors (JSON_QUERRY, JSON_VALUE(Metadata, $.Identifier)), the JSON is not valid. Does $.Metadata always have fixed count of items? — Zhorov
– Zhorov, Commented Jul 3, 2020 at 8:48
If the second row from the myTable table has $.Metadata JSON array with five items, what is the expected result for both rows? — Zhorov
– Zhorov, Commented Jul 3, 2020 at 9:20
I added an example in the question. if JSON values don't exist, it should returns NULL values. — Paul Pascher
– Paul Pascher, Commented Jul 3, 2020 at 9:35

critical_error · Accepted Answer · 2020-07-03 21:18:36Z

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.

Thanks for the detailed explanation! I managed to solve it with Dynamic SQL. Although I hoped for a solution with in the JSON handling. I still have to test how well it works with bigger Data. The final result has to run on a small industrial PC, can have thousands of objects and overall there might be 50 different Metatags. But I'm sure this is still better than handling it in C# Code afterwards.
You're welcome. I understand the frustration with working with dynamic SQL, as it's pretty much a last resort for me. There may be a better way to do this, but typically, when you're PIVOTing unknown data, there aren't a lot of other options. Hopefully, it works well on your smaller PC.

Collectives™ on Stack Overflow

SQL JSON_VALUE / JSON_QUERY From Array and transposed to Row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related