如何在使用 explicit schema 的 openjson 查询中映射键名？

Question

我有一个查询，它需要一个由一百个数组组成的数组，每个数组中包含一系列的值。JSON大致长这样：

[["horse",6],
 ...,
 ["dog",100]]

每个数组元素直接映射到另一个表格。虽然我可以使用以下方法进行更新，但我更希望能够使用明确的模式。

update some_table
set favorite_animal=convert(varchar(50),json_value(value,'strict $[0]'))
   ,favorite_number=convert(int,json_value(value,'strict $[1]'))
from openjson(@json)
where id = convert(int,[key])

很不幸，当我使用显式模式时，我不确定如何指定当前路径（[key]中的值）。 我非常希望能做类似这样的事情，但我找不到语法：

from openjson(@json) 
with (favAnimal varchar(50) '$[0]',favNumber int '$[1]', row int '[key]')

- Michael B

Answer 1

最初的回答：

下一个方法，这是一种可能的解决方案，是使用 OPENJSON() 和两个附加的 CROSS APPLY 操作符来处理嵌套 JSON 数组中索引为 0 和 1 的元素。

T-SQL:

DECLARE @json nvarchar(max)
SET @json = N'[["horse",6],["cat",10],["dog",100]]'

SELECT 
   (j.[key] + 1) AS row,
   j1.[value] AS favAnimal,
   j2.[value] AS favNumber
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON(j.[value]) j1
CROSS APPLY OPENJSON(j.[value]) j2
WHERE (j1.[key] = 0) AND (j2.[key] = 1)

结果：

-------------------------
row favAnimal   favNumber
-------------------------
1   horse       6
2   cat         10
3   dog         100

更新：

我认为甚至这样也可以工作：

DECLARE @json nvarchar(max)
SET @json = N'[["horse",6],["cat",10],["dog",100]]'

SELECT 
   (j1.[key] + 1) AS [row], 
   j2.*
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(CONCAT(N'[', j1.[value], N']')) WITH (
   favAnimal nvarchar(50) '$[0]',
   favNumber int '$[1]'
) j2

结果：

-------------------------
row favAnimal   favNumber
-------------------------
1   horse       6
2   cat         10
3   dog         100

- Zhorov

---

后者可以工作，但没有给我所需的索引器。我对前者的性能表示怀疑。 - Michael B

@MichaelB 我知道这可能是一个晚回答，但我使用了第二个解决方案来解决类似的问题。 - Zhorov