CODEWITHSUNDEEP
jsonsql-server
user1279779
user1279779

Reputation: 79

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

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

Upvotes: 3

Views: 688

Answers (3)

Alex Yu
Alex Yu

Reputation: 3537

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?

Upvotes: 2

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

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
;

Upvotes: 1

Nick.Mc
Nick.Mc

Reputation: 19204

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

Upvotes: 1

Related Questions