SQL Server- JSON object contains both strings and arrays: how to get result with single query?

Question

The data that I'm trying to parse sometimes contains a single string value, other times, it contains an array of values.

For example, this works fine:

SELECT
    color
FROM 
    OPENJSON('{"name": "Christmas Tree","color": "Green"}') 
WITH (color NVARCHAR(MAX) '$.color' )

But if I have an array in there instead, it fails:

SELECT
    color
FROM 
    OPENJSON('{"name": "Candy Cane","color": ["Red","White"]}') 
WITH    (color  NVARCHAR(MAX) '$.color')

I can fix that by adding "AS JSON" inside the OPENJSON call:

SELECT
    color
FROM 
    OPENJSON('{"name": "Candy Cane","color": ["Red","White"]}') 
WITH    (color  NVARCHAR(MAX) '$.color' AS JSON)

But now the original call breaks:

SELECT
    color
FROM 
    OPENJSON('{"name": "Christmas Tree","color": "Green"}') 
WITH (color NVARCHAR(MAX) '$.color' AS JSON)

Is there a way to handle both cases in one shot? Or do I have to try one, check for a null result, then try the other?

Changing the data to be consistent isn't an option, I'm dealing with someone else's data.

I'm eventually turning the single string value into an array that contains a single value, so it's ok to return the data that way (ie, it's ok to get ['Green'] instead of 'Green')

DB-Fiddle here: https://www.db-fiddle.com/f/929e5vraAp9vsJGFPXRf5F/1

The 'trivial' solution is to UNION ALL the two queries then filter the one that doesn't have NULL. But this is probably not useful for real life examples — Nick.Mc
– Nick.Mc, Commented Jan 10, 2019 at 23:44
Unfortunately, I'm trying to assign the result to a variable, so I can't use UNION ALL directly. I guess I could dump it to a temp table, then assign the variable from there. — user1279779
– user1279779, Commented Jan 11, 2019 at 0:03
You can assign it to a variable. Lets have a race and see who works it out first ;) — Nick.Mc
– Nick.Mc, Commented Jan 11, 2019 at 0:21

Alex Yu · Accepted Answer · 2019-01-11 01:21:21Z

2

You can decode one json field in several forms in one pass:

DECLARE @data varchar(max)
SET @data = '[{"name": "Christmas Tree","color": "Green"}, {"name": "Christmas Tree","color": ["Green", "Yellow"]}]'


SELECT
    ISNULL(color_array, color_scalar) as colour
FROM 
    OPENJSON(@data) 
WITH (
color_array nvarchar(max) '$.color' as JSON,
color_scalar nvarchar(max) '$.color')

Is it what you want?

answered Jan 11, 2019 at 1:21

Alex Yu

3,5771 gold badge27 silver badges42 bronze badges

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

2 Comments

user1279779 Over a year ago

This works, and is a bit cleaner than the UNION ALL solution. Thanks!

Alex Yu Over a year ago

Welcome to stackoverflow! I can compliment you: your question was surprisingly good for first attempt!

Nick.Mc · Accepted Answer · 2019-01-11 00:27:07Z

1

This will do what you want. Again it's probably too complicated for real world situations but it may give you some insight

DECLARE @JSON VARCHAR(100)
DECLARE @V VARCHAR(50)

SET @JSON = '{"name": "Candy Cane","color": ["Red","White"]}'


SELECT @V = colour
FROM (
SELECT
    color as colour
FROM 
    OPENJSON(@JSON) 
WITH    (color  NVARCHAR(MAX) '$.color')  
UNION ALL
SELECT
    color as colour
FROM 
    OPENJSON(@JSON) 
WITH    (color  NVARCHAR(MAX) '$.color' AS JSON)  
) ST
WHERE colour IS NOT NULL
ORDER BY colour

SELECT @V

It's important to note the ORDER BY. If this dataset ever returns two valid rows, you will at least consistently get the same answer.

Also I couldn't help renaming to colour ;)

answered Jan 11, 2019 at 0:27

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

3 Comments

user1279779 Over a year ago

This certainly will work for my purposes, and sadly, it's not going to be the ugliest portion of my code so far :) If no one comes up with a more elegant solution, I'll mark it as a solve.

Nick.Mc Over a year ago

haha yes JSON to relational will always be ugly because a consistent schema definition is just a can being kicked up the road to the database guy.

Alex Yu Over a year ago

It's possible to decode color as 2 fields: scalar and array variants in one pass. After that we can substitute UNIONs with ISNULLs

Vitaly Borisov · Accepted Answer · 2019-01-11 01:20:29Z

1

A little bit dodgy, however it works:

DECLARE @json NVARCHAR(MAX) = N'{"name": "Christmas Tree","color": "Green"}';
;WITH Data AS (
    SELECT CASE WHEN ISJSON(j.value) > 0 THEN j.[value] ELSE '["'+j.value+'"]' END AS [value] 
    FROM OPENJSON(@json) j 
    WHERE j.[key] = 'color'
)
SELECT d.[value] AS [color] 
FROM Data j 
CROSS APPLY OPENJSON(j.[value]) d
;

answered Jan 11, 2019 at 1:20

Vitaly Borisov

1,1932 gold badges14 silver badges22 bronze badges

Collectives™ on Stack Overflow

SQL Server- JSON object contains both strings and arrays: how to get result with single query?

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related