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: (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
– Yitzhak Khabinsky, Commented Mar 24, 2022 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
– SOS, Commented Mar 24, 2022 at 21:45
Does this answer your question? SQL Server query JSON Array In Short, use CROSS APPLY with OPENJSON dbfiddle.uk/… — SOS
– SOS, Commented Mar 24, 2022 at 21:51

Harry · Accepted Answer · 2022-03-24 21:52:56Z

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, 2022 at 21:52

Harry

2,9691 gold badge23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

romfir · Accepted Answer · 2022-03-24 22:08:44Z

0

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

answered Mar 24, 2022 at 22:08

romfir

4023 silver badges7 bronze badges

Collectives™ on Stack Overflow

Parse JSON in table with multiple ids

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related