Convert JSON value in SQL table to multiple columns

Question

One of my SQL table has two columns in the below format. Column "Parameters" in JSON Format

Value	Parameters
999-99-9999	{"A":null,"B":"dfsnl","C":"dfdfs","D":"dsfdfs","E":"","F":"dfssdfsdfdsf", "G":null,"H":"dfdsf,dfssfddfsdsffds","I":"73834874378","J":null,"K":null}

I am wondering if the column Parameter could be split into multiple column and hoping to achieve below format

Value	A	B	C	D	E	F	G	H	I	J	K
999-99-9999		dfsnl	dfdfs	dfdfs		dfssdfsdfdsf		dfdsf,dfssfddfsdsffds	73834874378

Below query does not seem to work for me. Help please?

SELECT t.Value , t.Parameters       , P.*
from MYTABLE t
CROSS APPLY OPENJSON(Parameters) 
WITH (
A varchar(10),
B varchar(10),
C varchar(10),
D varchar(10),
E varchar(10),
F varchar(10),
G varchar(10),
H varchar(10),
I varchar(10),
J varchar(10),
K varchar(10)
) P;

Answer 1

Use openjson to turn the JSON into a set of rows and columns. By default this will make a row for each key/value pair. If you want one row with named columns, use with and give it a schema and JSON queries for each column.

cross apply it with the table to combine the JSON columns with real columns.

SELECT value, a, b, c
FROM test
CROSS APPLY OPENJSON (parameters) with(
  a nvarchar(255) '$.A', b nvarchar(255) '$.B', c nvarchar(255) '$.C'
)

Demonstration.

See examples 3 and 4 in the openjson docs and JSON Data in SQL Server for more.