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?
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;
@JSON with your column name, @HelloWorld (obviously with the table already referenced in the FROM).I for Images.