MS SQL Server 2016 using JSON methods to get values

Question

It is probably something stupid what I did not see, but I can't manage to get JSON_VALUE or JSON_QUERY to work as I expect how it should work:

DECLARE @json nvarchar(max) = N'{"1031":"test-de","1033":"test-en","1036":"test-fr"}'

SELECT *
FROM OPENJSON(@json)

returns correct 3 rows.

DECLARE @json nvarchar(max) = N'{"1031":"test-de","1033":"test-en","1036":"test-fr"}'
SELECT ISJSON(@json) is_json
  , JSON_VALUE(@json, '$') label
  , JSON_QUERY(@json, '$') label2

returns 1 row, but label is NULL and label2 shows the whole string. OK, but:

DECLARE @json nvarchar(max) = N'{"1031":"test-de","1033":"test-en","1036":"test-fr"}'
SELECT ISJSON(@json) is_json
  , JSON_VALUE(@json, '$.1031') label
  , JSON_QUERY(@json, '$.1033') label2

returns 0 rows.

EDIT

I did expect to get results like this:

is_json, label, label2
1, test-de, test-en

What resultset are you after then?

Thom A
– Thom A ♦

2019-06-07 09:48:02 +00:00
Commented Jun 7, 2019 at 9:48 — Thom A
– Thom A ♦, Commented Jun 7, 2019 at 9:48
@Larnu updated question to clarify

YvesR
– YvesR

2019-06-07 09:50:20 +00:00
Commented Jun 7, 2019 at 9:50 — YvesR
– YvesR, Commented Jun 7, 2019 at 9:50

Thom A · Accepted Answer · 2019-06-07 09:52:47Z

1

As your Key starts with a number, you need to quote it. I think what you're after is:

DECLARE @json nvarchar(max) = N'{"1031":"test-de","1033":"test-en","1036":"test-fr"}'

SELECT ISJSON(@json) AS is_json,
       JSON_VALUE(@json, '$."1031"') AS label,
       JSON_VALUE(@json, '$."1033"') AS label2;

Alternatively you could use a WITH:

DECLARE @json nvarchar(max) = N'{"1031":"test-de","1033":"test-en","1036":"test-fr"}'

SELECT ISJSON(@json) is_json,
       J.label1,
       J.label2
FROM OPENJSON(@json)
     WITH (label1 varchar(7) '$."1031"',
           label2 varchar(7) '$."1033"') J;

answered Jun 7, 2019 at 9:52

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS SQL Server 2016 using JSON methods to get values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related