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?

Answer 1

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;