I have this JSON array, which is stored in an SQL Server table column named [json]
. The table only has this one column.
CREATE TABLE MyTable (
[json] nvarchar(200)
);
INSERT INTO MyTable
VALUES('[{"Id":1},{"Id":2},{"Id":3}]');
db<>fiddle here
Output I need: Each Id in a separate row under the same column using a SELECT statement:
Id (column name)
----------------
1
2
3
What I tried:
SELECT JSON_VALUE([json], '$.Id') as Id
FROM table
But the result was empty:
Id
--------
null
CodePudding user response:
It's faily simple to get this using OpenJson with a WITH to identify the object you want to retrieve
declare @json NVarChar(2048) = N'[{"Id":1},{"Id":2},{"Id":3}]';
SELECT * FROM OpenJson(@json)
WITH (ID INT '$.Id');
CodePudding user response:
You can use OPENJSON with JSON_VALUE like this:
CREATE TABLE MyTable (
[json] nvarchar(200)
);
INSERT INTO MyTable VALUES ('[{"Id":1},{"Id":2},{"Id":3}]'), ('[{"Id":1},{"Id":2},{"Id":3}]')
SELECT JSON_VALUE(a.[Value], '$.Id') Id
FROM MyTable f
CROSS APPLY OPENJSON (f.[json]) a