Having trouble with MS Sql Server OPENJSON feature

Question

I created a sql test script below for this question.

 IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;

CREATE TABLE #temp
(
    Id INT NOT NULL PRIMARY KEY
  , Attributes NVARCHAR(MAX) NULL
);

INSERT INTO #temp (Id, Attributes)
VALUES (1, '[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]');
INSERT INTO #temp (Id, Attributes)
VALUES (2, '[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]');
INSERT INTO #temp (Id, Attributes)
VALUES (3, '[{"Name":"State","Value":"Active"}]');
INSERT INTO #temp (Id, Attributes)
VALUES (4, '[{"Name":"Step","Value":"D"}]');

SELECT
    t.Id
  , t.Attributes
  , stepname.Value AS [Step]
  , statename.Value AS [State]
FROM #temp t
    CROSS APPLY
    OPENJSON(t.Attributes)
    WITH
    (
        Name NVARCHAR(MAX) '$.Name'
      , Value NVARCHAR(MAX) '$.Value'
    ) AS stepname
    CROSS APPLY
    OPENJSON(t.Attributes)
    WITH
    (
        Name NVARCHAR(MAX) '$.Name'
      , Value NVARCHAR(MAX) '$.Value'
    ) AS statename
WHERE 1 = 1
      --AND (stepname.Name = statename.Name)     -- A
      --AND                                      -- B
      --(                                        -- B
      --    stepname.Name IS NULL                -- B
      --    OR stepname.Name = 'Step'            -- B
      --)                                        -- B
      --AND                                      -- B
      --(                                        -- B
      --    statename.Name IS NULL               -- B
      --    OR statename.Name = 'State'          -- B
      --);                                       -- B

Running this script as is gives the following output:

Id	Attributes	Step	State
1	[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]	A	A
1	[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]	A	Active
1	[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]	Active	A
1	[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]	Active	Active
2	[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]	B	B
2	[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]	B	Inactive
2	[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]	Inactive	B
2	[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]	Inactive	Inactive
3	[{"Name":"State","Value":"Active"}]	Active	Active
4	[{"Name":"Step","Value":"D"}]	D	D

What I would like to see in the output though is only 4 rows, one per data row.

Id	Attributes	Step	State
1	[{"Name":"Step","Value":"A"},{"Name":"State","Value":"Active"}]	A	Active
2	[{"Name":"Step","Value":"B"},{"Name":"State","Value":"Inactive"}]	B	Inactive
3	[{"Name":"State","Value":"Active"}]		Active
4	[{"Name":"Step","Value":"D"}]	D

I have left some commented code in my example to see the types of things I've tried but all to no avail. Uncommenting the where statement with a '-- A' gets me closer, but not completely. I thought for sure that uncommenting the statements in the where clause with a '-- B' on the end would give me what I want, but it doesn't. Any ideas on how to do this?

I originally started out with only 1 OPENJSON block but had no success so I thought that perhaps having 2 OPENJSON blocks, one for the Step and one for the State would help, but still not able to get data rows 3 and 4 included since each of those rows is missing one of the 2 JSON values.

Thanks very much for any help!

Answer 1

Conditional aggregation could be used:

SELECT
    t.Id
  , t.Attributes
  , [Step] = MAX(CASE WHEN stepname.Name = 'Step' THEN stepname.Value END)
  , [State] = MAX(CASE WHEN statename.Name = 'State' THEN statename.Value END) 
FROM #temp t
CROSS APPLY OPENJSON(t.Attributes) WITH (
        Name NVARCHAR(MAX) '$.Name'
      , Value NVARCHAR(MAX) '$.Value'
    ) AS stepname
CROSS APPLY OPENJSON(t.Attributes) WITH (
        Name NVARCHAR(MAX) '$.Name'
      , Value NVARCHAR(MAX) '$.Value'
    ) AS statename
GROUP BY t.Id, t.Attributes
ORDER BY t.Id;

db<>fiddle demo