是否可以在 Sql Server 2016 中将来自多个数组元素的 json 文档的部分组合成单个结果?
给定这个 json:
{
"fruit": {
"types": [
{
"possible": [ "Apples", "Bananas", "Pears" ],
"category": "Basic"
},
{
"possible": [ "Oranges", "Grapefruit", "Lemons", "Limes" ],
"category": "Citrus"
},
{
"possible": [ "Blueberries", "Strawberries", "Cherries" ],
"category": "Berries"
}
]
}
}
我想查看可能元素中所有值的单个结果:
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'))
但这依赖于将查询与数组中的元素数量联系起来。
有没有一种方法可以做到这一点而不必单独指定每个数组元素?像这样的东西(这些都不是有效的表达式):
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types[].possible'))
or
SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types.possible'))
这是我应该进行交叉申请的地方吗?