Extracting JSON_VALUE out of json array

Question

This works fine if we have just one object:

SELECT JSON_VALUE('{"Name": "Test"}', '$.Name'); Output: Test

How can I get every Name property from json array in MS SQL?

SELECT JSON_VALUE('[{"Name": "Test"},{"Name": "Test2"}]', '$.Name'); Expected Output: 2 rows, Test and Test2

As you're wanting rows as output you'll need to use OPENJSON with CROSS APPLY instead of the scalar functions (like JSON_VALUE). — Dai
– Dai, Commented Oct 21, 2022 at 15:11

Dai · Accepted Answer · 2022-10-21 15:20:19Z

As you're wanting rows as output you'll need to use OPENJSON (maybe with CROSS APPLY) instead of the scalar functions (like JSON_VALUE).

If your JSON is in a scalar T-SQL variable or parameter (e.g. `@jsonText`) then you can do this:

DECLARE @jsonText nvarchar(max) = N'[{"Name": "Test"},{"Name": "Test2"}]';

SELECT
    j."Name"
FROM
    OPENJSON( @jsonText ) WITH (
        "Name" nvarchar(256) '$.Name'
    ) AS j;

If it's in a table column, use `CROSS APPLY`, like so:

DECLARE @t TABLE (
    JsonBlargh nvarchar(max) NOT NULL
);

INSERT INTO @t ( JsonBlargh ) VALUES
( N'[{"Name": "Test"},{"Name": "Test2"}]' ),
( N'[{"Name": "Test3"},{"Name": "Test4"}]' ),
( N'[{"Name": "Test5"},{"Name": "Test6"}]' );

-----------

SELECT
    j."Name"
FROM
    @t AS t
    CROSS APPLY OPENJSON( t.JsonBlargh/*, '$.Name'*/ ) WITH (
        "Name" nvarchar(256) '$.Name'
    ) AS j

Collectives™ on Stack Overflow

Extracting JSON_VALUE out of json array

1 Answer 1

If your JSON is in a scalar T-SQL variable or parameter (e.g. `@jsonText`) then you can do this:

If it's in a table column, use `CROSS APPLY`, like so:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

If your JSON is in a scalar T-SQL variable or parameter (e.g. @jsonText) then you can do this:

If it's in a table column, use CROSS APPLY, like so:

Comments

Your Answer

Sign up or log in

Post as a guest

Related

If your JSON is in a scalar T-SQL variable or parameter (e.g. `@jsonText`) then you can do this:

If it's in a table column, use `CROSS APPLY`, like so: