Extracting JSON_VALUE out of json array-CodePudding

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

CodePudding user response：

As you're wanting rows as output

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

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

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