JSON_VALUE path "$" returns NULL when it contains data

Question

I have a column data consisting of {"name":["John","Peter"],id:["20","30"]}

If I do

SELECT JSON_VALUE(data,'$.name[0]') from table

it returns John but doing

SELECT JSON_VALUE(data,'$') from db
SELECT JSON_VALUE(data,'$.name') from table

returns NULL in both.

How come it does not return:

{"name":["John","Peter"],id:["20","30"]}
["John","Peter"]

$.name doesn't contain a scalar value, it contains an array. What are the actual results you are after here? If you simply want the JSON data, then don't use JSON_VALUE, just reference data. (Also, I hope you don't have a table called db, that's a little confusing; a table isn't a database, a table is a database object.) — Thom A
– Thom A ♦, Commented Oct 8, 2020 at 12:48
I'm trying to extract the array of name (based on learn.microsoft.com/en-us/sql/relational-databases/json/…). Agreed with the db- I have changed it now for clarification. — CutePoison
– CutePoison, Commented Oct 8, 2020 at 13:02

Eldar · Accepted Answer · 2020-10-08 13:02:02Z

1

As mentioned in the remarks section of the JSON_VALUE documentation there is a table that says for tags array in the json says: Use JSON_QUERY instead.

SELECT json_query(j,'$.name') from a;

Fiddle

answered Oct 8, 2020 at 13:02

Eldar

10.9k2 gold badges20 silver badges40 bronze badges

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

4 Comments

CutePoison Over a year ago

Just what I needed - short question; do you know how to get the last element of an array, like json_value(j,'$.name[-1]')' ?

Eldar Over a year ago

@CutePoison well, there is no straight forward solution for that but you can check this so question

Thom A Over a year ago

That's a different question, @CutePoison , and should be posted as a new question but not difficult; though you would use OPENJSON rather than JSON_QUERY().

shlomiLan Over a year ago

Most simple answer, thanks

Collectives™ on Stack Overflow

JSON_VALUE path "$" returns NULL when it contains data

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related