How to parse JSON without key name in SQL Server

Question

I have JSON values like following in a column. I tried parsing it using OPENJSON.

DECLARE @json NVARCHAR(MAX)='[[108,290,1388,2056],[108,290,1388,2057]]'
DECLARE @json2 NVARCHAR(MAX)='{"1":29893,"2":1}'
DROP TABLE IF EXISTS #T1
SELECT @json AS [json] INTO #t1
INSERT INTO #t1 ([json]) SELECT @json2
SELECT * FROM #T1
CROSS APPLY OPENJSON([json]) 
WHERE ISJSON([json]) > 0

enter image description here

@json2 is returned as expected since it has key value pair. But @json needs another level of parsing since it does not have a key name, I could not parse it using JSON_VALUE.

Doing CROSS APPLY again is resulting in error for @json2 since it would not be in pair now (for value 29893).

SELECT * FROM #T1
CROSS APPLY OPENJSON(JSON) A
CROSS APPLY OPENJSON(value) B
WHERE ISJSON([JSON]) > 0

enter image description here

Msg 13609, Level 16, State 4, Line 66
JSON text is not properly formatted. Unexpected character '2' is found at position 0.

Is there a simpler way to do it for JSON objects without key names where I do not need to apply two separate logics for different formats?

• Yano_of_Queenscastle: "JSON objects without key names" are better known as JSON arrays.

Answer 1

Doing CROSS APPLY again is resulting in error for @json2 since it would not be in pair now (for value 29893).

You could simply filter the values in the second APPLY that to those that are valid JSON:

SELECT 
 #t1.[json] AS OriginalJson,
 ISNULL(sec.[value], frst.[value]) AS MyValue
FROM #T1 
 OUTER APPLY OPENJSON([json]) frst
 OUTER APPLY (
 SELECT *
 FROM OPENJSON(frst.[value]) scnd
 WHERE ISJSON(frst.[value]) = 1
 ) sec
WHERE ISJSON([json]) > 0

(I have also changed your CROSS APPLYs to OUTER APPLYs. You might need some filtering in WHERE to correct that.)

• GAURAV RATHOD: Thank you @Yano_of_Queenscastle :)
• Peter Vandivier: Courtesy fiddle: dbfiddle.uk/WNBds8Dq