SQL Server查询JSON数组

Question

我正在尝试在SQL Server 2016中查询下面这个具有特定结构的JSON，我想找到所有'key'具有特定值的记录。请问如何实现？

{
    "nodeDataArray": [{
        "key": "5B502176-E51A-48B7-B8F0-350984CFBCF2",
        "category": "IFM"
    }, {
        "key": "1260263E-6111-47B2-9776-FE9BA5C90DCB",
        "category": "IFM"
    }, {
        "key": "8AE454D3-944E-47BE-8CA9-049318DE213B",
        "category": "IFM"
    }, {
        "key": "96B20972-F88C-44BA-84AA-C1F45BE5C7D5",
        "category": "IFM"
    }
    }]
}

感谢您，Matt

- mnt

---

你使用的是哪个SQL Server版本？这个查询在该版本上是否保证可运行？ - Ido Gal

如果您发布代码或数据示例，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码示例”按钮（ {}）以使其得到良好的格式化和语法突出显示！ - marc_s

SQL Server 2016 版本 - mnt

Answer 1

DECLARE @json NVARCHAR(MAX)


SET @json = N'{
    "nodeDataArray": [  
    {
       "key": "5B502176-E51A-48B7-B8F0-350984CFBCF2",
        "category": "IFM"
    }, 
    {
        "key": "1260263E-6111-47B2-9776-FE9BA5C90DCB",
        "category": "IFM"
    }, 
    {
        "key": "8AE454D3-944E-47BE-8CA9-049318DE213B",
        "category": "IFM"
    }, 
    {
        "key": "96B20972-F88C-44BA-84AA-C1F45BE5C7D5",
        "category": "IFM"
    }
]
}'

SELECT  
    JSON_VALUE(nda.value, '$.key') AS [key],
    JSON_VALUE(nda.value, '$.category') AS [category]
FROM OPENJSON(@json, '$.nodeDataArray') AS nda
WHERE  JSON_VALUE(nda.value, '$.key')   = '1260263E-6111-47B2-9776-FE9BA5C90DCB'

- David May

---

接近了，但这不是我想要的结构。我有一个包含值的nodeDataArray数组... - mnt

Answer 2

SELECT tj.*
FROM TableName t
CROSS APPLY OPENJSON (t.JsonColumn, '$.nodeDataArray')
  WITH (
    key VARCHAR(300) '$.key',
    category VARCHAR(300) '$.category'
  ) AS tj

来源: Microsoft SQL 文档.

- Akira Yamamoto

Answer 3

如果你使用的是SQL 2016版本，请查看该文章：JSON Data (SQL Server)。

如果你没有使用SQL 2016版本，那么就没有原生的JSON支持。你最好的选择是在.NET中编写一些代码，并从SQL存储过程或函数中调用它（Google可以帮助你入门）。

- Brian

---

我正在使用SQL Server 2016。问题实际上是围绕直接查询数据库以查找所有记录（我们有几百个包含JSON数据的记录），这些记录包含某个键的特定值。对此的语法对我来说不太清楚。当然，我可以编写一个 .Net 函数来完成这个任务，但我想知道是否可以简单地发送一个查询以获取所需信息... - mnt