CODEWITHSUNDEEP
sqljsonsql-serversql-server-2012json.net
Shalini Samipillai
Shalini Samipillai

Reputation: 21

Split a column in a table which is in JSON format in to multiple column using SQL server

There is one specific column which holds data in JSON format, i need help in splitting the single column in to multiple column as given below,

Column value in a table: ('{"Date":"31.12.2020","Value":208983916.71000000000}')

Expected output:

Date Value

31.12.2020 208983916.7

Upvotes: 1

Views: 4006

Answers (1)

Charlieface
Charlieface

Reputation: 71144

You can use JSON_VALUE for this

SELECT
    Date =  JSON_VALUE(t.YourCol, '$.Date'),
    Value = JSON_VALUE(t.YourCol, '$.Value')
FROM YourTable t;

Alternatively, you can use OPENJSON (more useful if you have many columns)

SELECT
    j.Date,
    j.Value
FROM YourTable t
CROSS APPLY OPENJSON(t.YourCol)
  WITH ( Date varchar(20), Value decimal(20,11) ) AS j;

Upvotes: 2

Related Questions