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!

Solution

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!