T-sql extract json string to multiple columns

kaismund
March 14, 2023
151 views
1 vote
2 Answers

I have one table which contains one JSON string column. How can I extract the information this into several different columns?

The json column values look something like this:

[{"Name":"Id1","Value":"11ea1111-cc22-3bb3-a33d-62159f192eba"}, 
 {"Name":"Id2","Value":"b2222222-7777-00eb-0cc1-12345687bbbb"},  
 {"Name":"Id3","Value":"5d65d2c1-151e-41b4-af00-12345687aaaa"}, 
 {"Name":"Id4","Value":"5bbd0da5-7698-4fa6-a893-9874654123aa"},  
 {"Name":"Quantity","Value":"10"}, 
 {"Name":"Id5","Value":"d22222-8877-4558-ah32-789456123"}]

I would like to extract the information to the columns like in the picture:

I am using Microsoft SQL Server. I have tried to use JSON_VALUE, but it only returns null values.

Answers

Try this:

SELECT 
    Id, 
    JSON_VALUE(JsonData, '$[0].Value') AS Id1,
    JSON_VALUE(JsonData, '$[1].Value') AS Id2,
    JSON_VALUE(JsonData, '$[2].Value') AS Id3,
    JSON_VALUE(JsonData, '$[3].Value') AS Id4,
    JSON_VALUE(JsonData, '$[4].Value') AS Quantity,
    JSON_VALUE(JsonData, '$[5].Value') AS Id5
FROM [my_table]

You can use the following to get the pairs values in a column format:

SELECT *
FROM
(
SELECT id, DENSE_RANK() OVER(ORDER BY json.value), j.[key] , j.[value]
FROM myTable
CROSS APPLY OPENJSON(JsonData) AS json
CROSS APPLY OPENJSON(json.value) AS j
) DS (id, row_id, col, val)
PIVOT
(
    MAX(val) for col IN ([Name], [Value])
) PVT
ORDER BY id, row_id

It will give you something like this:

but if you want to PIVOT the data, you will need dynamic T-SQL.

A dynamic PIVOT using the parsed JSON content is another option:

Test data:

DECLARE @json1 nvarchar(max) = N'[
   {"Name":"Id1","Value":"11ea1111-cc22-3bb3-a33d-62159f192eba"}, 
   {"Name":"Id2","Value":"b2222222-7777-00eb-0cc1-12345687bbbb"},  
   {"Name":"Id3","Value":"5d65d2c1-151e-41b4-af00-12345687aaaa"}, 
   {"Name":"Id4","Value":"5bbd0da5-7698-4fa6-a893-9874654123aa"},  
   {"Name":"Quantity","Value":"10"}, 
   {"Name":"Id5","Value":"d22222-8877-4558-ah32-789456123"}
]'
DECLARE @json2 nvarchar(max) = N'[
   {"Name":"Id1","Value":"11ea1111-cc22-3bb3-a33d-62159f192eba"}, 
   {"Name":"Id7","Value":"d22222-8877-4558-ah32-789456123"}
]'
SELECT *
INTO JsonTable
FROM (VALUES 
  (1, @json1),
  (2, @json2)
) v (Id, JsonColumn)

T-SQL:

-- Different columns names
DECLARE @cols nvarchar(max)
SELECT @cols = STRING_AGG(QUOTENAME(s.[Name]), N',')
FROM (
   SELECT DISTINCT JSON_VALUE(j.[value], '$.Name') AS [Name]
   FROM JsonTable t
   CROSS APPLY OPENJSON(t.JsonColumn) j
) s

-- Dynamic PIVOT
DECLARE @stmt nvarchar(max)
SELECT @stmt = 
   N'SELECT *
   FROM (  
      SELECT 
         t.Id,
         JSON_VALUE(j.[value], ''$.Name'') AS [Name],
         JSON_VALUE(j.[value], ''$.Value'') AS [Value]
      FROM JsonTable t
      CROSS APPLY OPENJSON(t.JsonColumn) j
   ) t
   PIVOT (
      MAX([Value]) FOR [Name] IN (' + @cols + N')
  ) p'

-- Execution
DECLARE @err int  
EXEC @err = sp_executesql @stmt
IF @err <> 0 PRINT 'Error'

Result:

Id	Id1	Id2	Id3	Id4	Id5	Id7	Quantity
1	11ea1111-cc22-3bb3-a33d-62159f192eba	b2222222-7777-00eb-0cc1-12345687bbbb	5d65d2c1-151e-41b4-af00-12345687aaaa	5bbd0da5-7698-4fa6-a893-9874654123aa	d22222-8877-4558-ah32-789456123	null	10
2	11ea1111-cc22-3bb3-a33d-62159f192eba	null	null	null	null	d22222-8877-4558-ah32-789456123	null

Please signup or login to give your own answer.

Click here to cancel reply.