Parse JSON in table with multiple ids-CodePudding

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