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?