Parse JSON in table with multiple ids

Question

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

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Mar 24 '22 at 21:05
@matt_vice89 - I updated the question to demonstrate a minimal example. Including that kind of example in future questions will make it easier to answer, and help avoid being closed. — SOS, Mar 24 '22 at 21:45
Does this answer your question? [SQL Server query JSON Array](https://stackoverflow.com/questions/46055471/sql-server-query-json-array) In Short, use CROSS APPLY with OPENJSON https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=958961cf2dbd7c6f1b7551a4365313b4 — SOS, Mar 24 '22 at 21:51

score 1 · Accepted Answer · answered Mar 24 '22 at 21:52

1

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');

answered Mar 24 '22 at 21:52

Harry

2,636
1
17
29

score 0 · Answer 2 · answered Mar 24 '22 at 22:08

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

Parse JSON in table with multiple ids

2 Answers2