Convert JSON value in SQL table to multiple columns
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;
Solution 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.