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
>Solution :
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
