使用Sql Server 2016的OPENJSON函数从Json文档中的多个数组元素中选择结果 [英] Select results from multiple array elements in Json document with Sql Server 2016's OPENJSON function
问题描述
在Sql Server 2016中是否可以将多个数组元素中的json文档的一部分合并为一个结果?
Is it possible to combine parts of a json document from multiple array elements into a single result in Sql Server 2016?
给出此json:
{
"fruit": {
"types": [
{
"possible": [ "Apples", "Bananas", "Pears" ],
"category": "Basic"
},
{
"possible": [ "Oranges", "Grapefruit", "Lemons", "Limes" ],
"category": "Citrus"
},
{
"possible": [ "Blueberries", "Strawberries", "Cherries" ],
"category": "Berries"
}
]
}
}
我想查看可能元素中所有值的单个结果:
I'd like to see a single result of all the values from the possible element:
results
-----
Apples
Bananas
Pears
Oranges
Grapefruit
Lemons
Limes
Blueberries
Strawberries
Cherries
我已经很接近了:
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[0].possible'))
UNION
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[1].possible'))
UNION
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[2].possible'))
但这依赖于将查询绑定到数组中元素的数量. 有没有一种方法可以不必单独指定每个数组元素?这样的东西(都不是有效的表达式):
But this relies on tying the query to the number of elements in the array. Is there a way to do it without having to specify each array element individually? Something like this (neither of these are valid expressions):
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[].possible'))
或
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types.possible'))
这是我应该进行交叉申请的地方吗?
Is this where I should be doing a CROSS APPLY?
推荐答案
这是我应该进行交叉申请的地方吗?
Is this where I should be doing a CROSS APPLY?
是的
declare @json nvarchar(max)='
{
"fruit": {
"types": [
{
"possible": [ "Apples", "Bananas", "Pears" ],
"category": "Basic"
},
{
"possible": [ "Oranges", "Grapefruit", "Lemons", "Limes" ],
"category": "Citrus"
},
{
"possible": [ "Blueberries", "Strawberries", "Cherries" ],
"category": "Berries"
}
]
}
}
'
select v.value
from openjson(@json, '$.fruit.types') t
cross apply openjson(t.value,'$.possible') v
输出
value
---------
Apples
Bananas
Pears
Oranges
Grapefruit
Lemons
Limes
Blueberries
Strawberries
Cherries
(10 row(s) affected)
这篇关于使用Sql Server 2016的OPENJSON函数从Json文档中的多个数组元素中选择结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!