How to read a complex json array with SQL Server

Question

This is my query:

SELECT
    JSON_QUERY(MyStringColumnWithJson, '$.Images') AS images
FROM MyTable

which returns a single field with the JSON data shown here below:

"{ 
   "Images":
   [
     {"Name":"test1.jpeg","Description":"originalName1.jpeg"}, 
     {"Name":"test2.jpeg","Description":"originalName2.jpeg"}, 
     {"Name":"test3.jpeg","Description":"originalName3.jpeg"}
   ]
}"

How can I read the images result row by row into a temporary table structure?

Thom A · Accepted Answer · 2021-12-13T16:31:00.260

3

Use OPENJSON which returns a data set, not JSON_VALUE, which returns a scalar value. For example:

DECLARE @JSON nvarchar(MAX) = N'{ 
"Images":
   [
     {"Name":"test1.jpeg","Description":"originalName1.jpeg"}, 
     {"Name":"test2.jpeg","Description":"originalName2.jpeg"}, 
     {"Name":"test3.jpeg","Description":"originalName3.jpeg"}
   ]
}';

SELECT *
FROM OPENJSON(@JSON, '$.Images')
     WITH (Name nvarchar(128),
           Description nvarchar(128))OJ;

SELECT I.[Name],
       I.Description
FROM MyTable MT
     CROSS APPLY OPENJSON(MT.YourJsonColumn, '$.Images')
                 WITH (Name nvarchar(128),
                       Description nvarchar(128)) I;

edited Dec 13 '21 at 16:31

answered Dec 13 '21 at 16:21

Thom A

88,727
11
45
75

this is like all samples I found... they base on a variable containing json... BUT my json is in an sql field! How would you do that? – HelloWorld Dec 13 '21 at 16:23
Just replace `@JSON` with your column name, @HelloWorld (obviously with the table already referenced in the `FROM`). – Thom A Dec 13 '21 at 16:23
what is OJ at the end? – HelloWorld Dec 13 '21 at 16:26
An Alias, @HelloWorld ... O for Open, J for JSON. You can use something else if you prefer. For example `I` for `Images`. – Thom A Dec 13 '21 at 16:26
sorry i dont get it...its really confusing. Do i have now 2 select statements? Can you pls use my original code and modify it , thx! – HelloWorld Dec 13 '21 at 16:28
Done. Like I said, just add the column to `OPENJSON`, @HelloWorld . – Thom A Dec 13 '21 at 16:31
nah... its not only add the column. Without Cross apply nothing makes sense. Anyway thanks! – HelloWorld Dec 13 '21 at 16:33
So are you saying the above doesn't work, @HelloWorld ..? – Thom A Dec 13 '21 at 16:34

How to read a complex json array with SQL Server

1 Answers1