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

Answer 1

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