Counting number of nested levels in JSON object in T-SQL returns not properly formatted error message

Question

The following script, in SSMS 19.3, throws this error:

JSON text is not properly formatted. Unexpected character '.' is found at position 1.

DECLARE @json NVARCHAR(MAX) = '{
    "level1": {
        "level2": {
            "level3": {
                "level4": "1"
            }
        },
        "level2B": {}
    },
    "level1B": {}
}';

-- Recursive CTE to find the depth of the JSON
WITH RecursiveCTE AS 
(
    SELECT 
        JSON_QUERY(@json) AS Obj, 
        1 AS Depth 
    WHERE 
        ISJSON(@json) > 0
    UNION ALL
    SELECT 
        JSON_QUERY(value) AS Obj, 
        Depth + 1 
    FROM 
        RecursiveCTE 
    CROSS APPLY OPENJSON(Obj) 
)
SELECT MAX(Depth) AS MaxDepth
FROM RecursiveCTE;

I cannot see this unexpected character "." at position 1. Where would that be? Anyone more experienced with this?

This is because eventually you will try to do: JSON_QUERY('1') which won't do. Try changing your CTE to: JSON_QUERY(case when isjson(value) = 1 then value end) AS Obj — siggemannen
– siggemannen, Commented Aug 2, 2024 at 13:57
You'll get that error message from SELECT JSON_QUERY('');. Since you only want to recurse on JSON objects returned from OPENJSON(), have you tried adding WHERE [type]=5 after CROSS APPLY OPENJSON(Obj)? — AlwaysLearning
– AlwaysLearning, Commented Aug 2, 2024 at 14:02

3 revs · Accepted Answer · 2024-08-04 10:53:25Z

The error message is a bit confusing here but you get this error when it gets down to processing { "level4": "1" } as the value of Obj in the recursive part.

SELECT value 
FROM OPENJSON('{"level4": "1"}')

Returns 1

If you do SELECT JSON_QUERY('1') then you do see a more helpful error message "JSON text is not properly formatted. Unexpected character '1' is found at position 0." but

SELECT JSON_QUERY(value)
FROM OPENJSON('{"level4": "1"}')

Does report . for some reason.

Two possible solutions for this have already been given in the comments.

"Change the expression to JSON_QUERY(case when isjson(value) = 1 then value end) AS Obj" (@siggemannen)
"Since you only want to recurse on JSON objects returned from OPENJSON(), have you tried adding WHERE [type]=5 after CROSS APPLY OPENJSON(Obj)?" (@AlwaysLearning)

Both suppress the error but they return different results for max depth (of 5 and 4 respectively).

You don't need to use JSON_QUERY here anyway though and can use

-- Recursive CTE to find the depth of the JSON
WITH RecursiveCTE AS 
(
    SELECT 
        @json AS Obj, 
        1 AS Depth 
    WHERE 
        ISJSON(@json) = 1
    UNION ALL
    SELECT 
        value AS Obj, 
        Depth + 1 
    FROM 
        RecursiveCTE 
    CROSS APPLY OPENJSON(Obj) 
    WHERE [type]=5
)
SELECT MAX(Depth) AS MaxDepth
FROM RecursiveCTE;

Collectives™ on Stack Overflow

Counting number of nested levels in JSON object in T-SQL returns not properly formatted error message

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related