I want to parse a json-array in my sql-server. I would like to use OPENJSON
with WITH
to parse specific values into columns. How can I get the index of each array item?
I know, that this works fine with JSON_VALUE
and without WITH
:
DECLARE @json NVARCHAR(MAX) = '[{"name":"Alpha"},{"name":"Bravo"},{"name":"Charlie"}]'
SELECT [key], JSON_VALUE(value, '$.name')
FROM OPENJSON(@json)
-- key | value
----------------------------------
-- 0 | Alpha
-- 1 | Bravo
-- 2 | Charlie
But when I add WITH
I seem to not have access to key
anymore.
SELECT [key], name
FROM OPENJSON(@json)
WITH (
[key] INT, -- does not work
name NVARCHAR(MAX)
)
-- key | name
----------------------------------
-- NULL | Alpha
-- NULL | Bravo
-- NULL | Charlie
You need to use OPENJSON()
with the default schema to get the key of each array item (as your first attempt). A note in the documentation explains:
Note: The Key, Value, and Type columns are returned only when you use OPENJSON with the default schema and are not available with an explicit schema.
The following statement is just another option to get the keys and parse the JSON using an explicit schema:
DECLARE @json NVARCHAR(MAX) = '[{"name":"Alpha"},{"name":"Bravo"},{"name":"Charlie"}]';
SELECT j1.[key], j2.*
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON (j1.[value]) WITH (name nvarchar(max)) j2
Result:
key name
---------
0 Alpha
1 Bravo
2 Charlie