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: ```sql -- 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.