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.

Answer 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;

Answer 2

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