在 SQL Server 中查询根为对象数组的 Json

Question

我有一个SQL表中的列，该列具有以下类似的JSON值：

[
  {"address":{"value":"A9"},
  "value":{"type":11,"value":"John"}}, 
  {"address":{"value":"A10"},
  "value":{"type":11,"value":"Doe"}}]

MSDN中对于JSON_VALUE或JSON_QUERY的示例要求根节点为json对象。如何查询返回"address"为A9且"value"为John的行？我正在使用SQL Azure。

- frosty

Answer 1

类似这样的：

declare @json nvarchar(max) = '[
  {"address":{"value":"A9"},
  "value":{"type":11,"value":"John"}}, 
  {"address":{"value":"A10"},
  "value":{"type":11,"value":"Doe"}}]'



select a.*
from openjson(@json) r
cross apply openjson(r.value)
with (   
        address  nvarchar(200)   '$.address.value',  
        name     nvarchar(200)  '$.value.value'
     ) a  
where address = N'A9'
  and name = N'John'

输出

address name
------- -----
A9      John

(1 row affected)

- David Browne - Microsoft

---

谢谢，就我的情况而言，json是SQL表中的一列。openjson(@json)能否使用列名而不是变量？与直接使用$.语法查询json对象相比，使用openjson的性能惩罚是什么？ - frosty

我可以使用以下代码中的openjson()函数中的列。由于我的表可能会增长到数亿行，因此了解性能影响将非常有用。SELECT * FROM [UserRecordData] P CROSS APPLY OPENJSON (P.Data) with (
address nvarchar(200) '$.address.value',
name nvarchar(200) '$.value.value' ) a
WHERE ISJSON(Data)>0 AND address = 'A9' AND name = 'John' - frosty

我觉得很有趣的是，使用这样的JSON可以说JSON_VALUE(@json, '$[0]') - 这将返回未命名数组的第一个元素。但是似乎不可能引用该元素内的键。例如，无法说JSON_VALUE('$[0].address.value') - 这非常令人沮丧！它是可能的，即显然有效，但始终返回NULL。 - El Ronnoco

如果JSON是一个简单的字符串数组，["a", "b", "c"]怎么办？我无法使其工作：select id from openjson(@json) r cross apply openjson(r.value) with (id varchar(10) '$') x;。它会报错，显示“'$'附近有语法错误。” :-( - Krishnan

非常奇怪。即使上面答案中的代码片段也会抛出相同的错误。“在‘$.address.value’附近有语法错误。” - Krishnan

好的。发现问题是因为 SQL Server 兼容性低于 130。参考：https://database.guide/openjson-incorrect-syntax-near-the-keyword-with-in-sql-server-solved/ - Krishnan

Answer 2

这可能与OP的帖子不完全相关，因为用法不同，但是可以从根级别未命名的JSON数组中检索任意项，例如：

declare @json nvarchar(max) = '[
  {"address":
    {"value":"A9"},
   "value":
    {"type":11,"value":"John"}
  }, 
  {"address":
    {"value":"A10"},
   "value":
    {"type":11,"value":"Doe"}
  }
]'

select
    JSON_VALUE(
        JSON_QUERY(@json, '$[0]'),
        '$.address.value') as 'First address.value',
    JSON_VALUE(
        JSON_QUERY(@json, '$[1]'),
        '$.address.value') as 'Second address.value'

输出：

First address.value         Second address.value
A9                          A10

- El Ronnoco