How do I get the "key" of an array item in OPENJSON, when using WITH?

Question

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

Zhorov · Accepted Answer · 2021-10-15T05:53:57.677

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

How do I get the "key" of an array item in OPENJSON, when using WITH?

1 Answers1