Retrieve json value into columns when values are not named

Question

I have a column named Username (varchar max, allows nulls) in a table. The table may have multiple entries that are all stored in this column. It is currently displayed in this format:

["name1","name2"]

I am trying to pull out name1 and name2 into separate rows. I cannot figure out how to do so. Here is the query that I currently have that is closest:

select u.id, a.[Username]
from dbo.Users u
CROSS APPLY OPENJSON(Username) WITH ([Username] varchar(max)) a

This is pulling them out in rows correctly but the rows display NULL instead of the value. I checked that the name matches the column name in syntax and it does.

You can name JSON value nested in array by passing a $ path in the WITH clause - SELECT a.Username FROM OPENJSON('["John","Kat"]') WITH ([Username] varchar(max) '$') a — Jan Madeyski
– Jan Madeyski, Commented Oct 22, 2020 at 6:57

SteveC · Accepted Answer · 2020-10-15 15:22:59Z

1

The OPENJSON function is looking for a column named 'Username'. Instead, try giving OPENJSON 'oj' an alias and opening the JSON without specifying a schema. Then add oj.value to the SELECT list. Something like this

select u.id, oj.[value] as Username
from dbo.Users u
     cross apply openjson(u.Username) oj;

answered Oct 15, 2020 at 15:22

SteveC

6,0252 gold badges14 silver badges26 bronze badges

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

Comments

Zhorov · Accepted Answer · 2020-10-22 06:39:44Z

1

The reason for the unexpected results is explained in the documentation: "...OPENJSON matches keys in jsonExpression with the column names in with_clause (in this case, matches keys implies that it is case sensitive). If a column name does not match a key name, you can provide an optional column_path...". Your input JSON is an array of scalar values and there isn't a key "username".

But, you can use OPENJSON() with explicit schema (the WITH clause). Just use the correct path ('$' in your case).

Table:

CREATE TABLE Users (id int, username varchar(max))
INSERT INTO Users (id, username) VALUES (1, '["name1","name2"]')

Statement:

SELECT u.id, a.[username]
FROM Users u
CROSS APPLY OPENJSON (u.[username]) WITH ([username] varchar(max) '$') a

Result:

id  username
1   name1
1   name2

edited Oct 22, 2020 at 6:39

answered Oct 15, 2020 at 17:29

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Collectives™ on Stack Overflow

Retrieve json value into columns when values are not named

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