Apply OPENJSON to a single column

Question

I have a products table with two attribute column, and a json column. I'd like to be able to delimit the json column and insert extra rows retaining the attributes. Sample data looks like:

ID          Name          Attributes
1           Nikon        {"4e7a":["jpg","bmp","nef"],"604e":["en"]}
2           Canon        {"4e7a":["jpg","bmp"],"604e":["en","jp","de"]}
3           Olympus      {"902c":["yes"], "4e7a":["jpg","bmp"]}

I understand OPENJSON can convert JSON objects into rows, and key values into cells but how do I apply it on a single column that contains JSON data?

My goal is to have an output like:

ID          Name          key        value
1           Nikon         902c       NULL
1           Nikon         4e7a       ["jpg","bmp","nef"]
1           Nikon         604e       ["en"]
2           Canon         902c       NULL
2           Canon         4e7a       ["jpg","bmp"]
2           Canon         604e       ["en","jp","de"]
3           Olympus       902c       ["yes"]
3           Olympus       4e7a       ["jpg","bmp"]
3           Olympus       604e       NULL

Is there a way I can query this products table like? Or is there a way to reproduce my goal data set?

SELECT
  ID,
  Name,
  OPENJSON(Attributes)
FROM products

Thanks!

Answer 1

Here is something that will at least start you in the right direction.

SELECT P.ID, P.[Name], AttsData.[key], AttsData.[Value]
FROM products P CROSS APPLY OPENJSON (P.Attributes) AS AttsData

The one thing that has me stuck a bit right now is the missing values (value is null in result)...

I was thinking of maybe doing some sort of outer/full join back to this, but even that is giving me headaches. Are you certain you need that? Or, could you do an existence check with the output from the SQL above?

I am going to keep at this. If I find a solution that matches your output exactly, I will add to this answer.

Until then... good luck!

Answer 2

You can get the rows with NULL value fields by creating a list of possible keys and using CROSS APPLY to associate each key to each row from the original dataset, and then left-joining in the parsed JSON.

Here's a working example you should be able to execute as-is:

-- Throw together a quick and dirty CTE containing your example data
WITH OriginalValues AS (
    SELECT *
    FROM (
        VALUES ( 1, 'Nikon', '{"4e7a":["jpg","bmp","nef"],"604e":["en"]}' ),
        ( 2, 'Canon', '{"4e7a":["jpg","bmp"],"604e":["en","jp","de"]}' ),
        ( 3, 'Olympus', '{"902c":["yes"], "4e7a":["jpg","bmp"]}' )
    ) AS T ( ID, Name, Attributes )
),

-- Build a separate dataset that includes all possible 'key' values from the JSON.
PossibleKeys AS (
    SELECT DISTINCT A.[key]
    FROM OriginalValues CROSS APPLY OPENJSON( OriginalValues.Attributes ) AS A
),

-- Get the existing keys and values from the JSON, associated with the record ID
ValuesWithKeys AS (
    SELECT OriginalValues.ID, Atts.[key], Atts.Value
    FROM OriginalValues CROSS APPLY OPENJSON( OriginalValues.Attributes ) AS Atts
)

-- Join each possible 'key' value with every record in the original dataset, and
-- then left join the parsed JSON values for each ID and key
SELECT OriginalValues.ID, OriginalValues.Name, KeyList.[key], ValuesWithKeys.Value
FROM OriginalValues
CROSS APPLY PossibleKeys AS KeyList
LEFT JOIN ValuesWithKeys
    ON OriginalValues.ID = ValuesWithKeys.ID
    AND KeyList.[key] = ValuesWithKeys.[key]
ORDER BY ID, [key];

If you need to include some pre-determined key values where some of them might not exist in ANY of the JSON values stored in Attributes, you could construct a CTE (like I did to emulate your original dataset) or a temp table to provide those values instead of doing the DISTINCT selection in the PossibleKeys CTE above. If you already know what your possible key values are without having to query them out of the JSON, that would most likely be a less costly approach.