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

  Kiến thức lập trình

I am new to T-SQL.

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?

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT