I'm starting to work with the JSON operators in Sql Server 2019, and on an msdn page, I found a chunk talking about the $.sql:identity() operator and how that's supposed to give you an integer index into the json array you're processing.
The problem is that the example on the msdn page doesn't work.
This is the code from msdn
DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
{"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
]';
SELECT * FROM OPENJSON(@array)
WITH ( month VARCHAR(3),
temp int,
month_id tinyint '$.sql:identity()') as months
but when I try to run it, I get a parsing exception:
JSON path is not properly formatted. Unexpected character ':' is found at position 5.
Any clues? Is there a working way to get the array index in a json array as you go through it?
Thanks
Asked by user1664043
(379 rep)
Dec 14, 2023, 10:18 PM
Last activity: May 19, 2025, 05:03 PM
Last activity: May 19, 2025, 05:03 PM