Extracting JSON values from a SQL Server NVARCHAR column

Question

I'm trying to extract the values, but the always return NULL.

DECLARE @TestTable TABLE
(
    JSON_Column NVARCHAR(MAX)
)

INSERT INTO @TestTable
VALUES
(
    (SELECT 111111 AS idTest1, 222222 AS idTest2 FOR JSON PATH)
);

SELECT 
    JSON_Column,
    JSON_VALUE(JSON_Column,'$.idTest1') AS idTest1,
    JSON_VALUE(JSON_Column,'$.idTest2') AS idTest2

FROM @TestTable

Results:

[{"idTest1":111111,"idTest2":222222}]   NULL    NULL

Expected:

[{"idTest1":111111,"idTest2":222222}]   111111  222222

Martin · Accepted Answer · 2019-01-30 22:22:35Z

This looks to me to be an issue with your JSON not behaving as you expect.

Your JSON is an array containing the two values idTest1 and idTest2. Your path $.idTest1 doesn't work because the values exist inside the array entry. Look at the JSON output:

[{"idTest1":111111,"idTest2":222222}]  -- The square brackets are a dead giveaway!

Due to the array, you need to change your path to factor in the first element of it:

SELECT JSON_Column,
       JSON_VALUE(JSON_Column,'$[0].idTest1') AS idTest1,
       JSON_VALUE(JSON_Column,'$[0].idTest2') AS idTest2
  FROM @TestTable

Note that I have changed the path to $[0].idTest1. This will select the idTest1 value of the first element in the array.

Incidentally, if you had strict path matching enabled this would have given you an error (as it should) and may have helped you to diagnose the issue more quickly.

A working fiddle is here.

Kashif Qureshi · Accepted Answer · 2019-01-30 22:45:39Z

1

You can use WITHOUT_ARRAY_WRAPPER option on FOR JSON PATH clause like this:

DECLARE @TestTable TABLE
(
    JSON_Column NVARCHAR(MAX)
);

INSERT INTO @TestTable
VALUES
(
    (SELECT 111111 AS idTest1, 222222 AS idTest2 
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
),
(
    (SELECT 3333 AS idTest1, 44444 AS idTest2 
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
);

SELECT 
    JSON_Column,
    JSON_VALUE(JSON_Column,'$.idTest1') AS idTest1,
    JSON_VALUE(JSON_Column,'$.idTest2') AS idTest2
FROM @TestTable

Result:

JSON_Column                          idTest1    idTest2
{"idTest1":111111,"idTest2":222222}  111111      222222
{"idTest1":3333,"idTest2":44444}     3333        44444

answered Jan 30, 2019 at 22:45

Kashif Qureshi

1,4902 gold badges13 silver badges20 bronze badges

Collectives™ on Stack Overflow

Extracting JSON values from a SQL Server NVARCHAR column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related