SQL Server查询JSON数组 [英] SQL Server query JSON Array
本文介绍了SQL Server查询JSON数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用以下结构在SQL Server 2016中查询某些JSON,在该结构中我想查找键"具有特定值的所有记录.对如何执行此操作有帮助吗?
I am trying to query some JSON in SQL Server 2016 with the below structure where I would like to find all records where the 'key' has a certain value. Any help on how to do this?
{
"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"
}
}]
}
谢谢
马特
推荐答案
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'
这篇关于SQL Server查询JSON数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文