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

@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

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?

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