Transforming array of key value pairs in json to Table in SQL Server

Question

I have a table where one column is the id of some executed code and the other column is an array of key value pairs of the name of the parameters passed and the values Formatted like this

executionId|params                                                           |

200001     |[{"key":"name","value":"john"},{"key":"surname","value":"smith"}]|

where the amount of parameters passed is arbitrary and i want to output it formatted as a table.

I tried with this query

SELECT 
   t.executionId,
   j.*
FROM #tmpTransac t
CROSS APPLY (
   SELECT * 
   FROM OPENJSON(t.[params])
   ) j

But the output i get is this

executionId|key|value
1992013     0   {"key":"name","value":"john"}
1992013     1   {"key":"surname","value":"smith"}   

and i expected something like

executionId| name |surname
1992013    | john |smith

Answer 1

My suggestion uses OPENJSON including a WITH clause and PIVOT

--A mockup table

DECLARE @tbl TABLE(executionId INT,params NVARCHAR(MAX))
INSERT INTO @tbl VALUES(200001,'[{"key":"name","value":"john"},{"key":"surname","value":"smith"}]');

-the query

SELECT p.*
FROM
(
    SELECT t.executionId
          ,A.[key] AS ParamKey
          ,A.[value] AS ParamValue
    FROM @tbl t
    CROSS APPLY OPENJSON(t.params) WITH([key] NVARCHAR(MAX),[value] NVARCHAR(MAX)) A
) t
PIVOT(MAX(ParamValue) FOR ParamKey IN([Name],Surname)) p;

The idea in short:

It is a bit bewildering, that the JSON elements carry the same names as the columns returned by OPENJSON. but the WITH clause will look for these names within the JSON, so this will return a list like

name       john
surname    smith

To get this side-by-side you can use either PIVOT or conditional aggregation-

Answer 2

You need to pivot out your data, as it's in 2 separate JSON strings inside your larger value:

SELECT V.executionId,
       MAX(CASE N.[key] WHEN 'name' THEN N.[value] END) AS [name],
       MAX(CASE N.[key] WHEN 'surname' THEN N.[value] END) AS [surname]
FROM (VALUES(200001,'[{"key":"name","value":"john"},{"key":"surname","value":"smith"}]'))V(executionId, params)
     CROSS APPLY OPENJSON(V.params) OJ
     CROSS APPLY OPENJSON(OJ.[value]) 
                 WITH ([key] varchar(50) '$.key',
                       [value] varchar(100) '$.value') N
GROUP BY V.executionId;