使用Sql Server 2016的OPENJSON函数从Json文档中选择多个数组元素的结果

Question

是否可以在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')) 

或

SELECT * FROM OPENJSON(JSON_QUERY(@json, '$.fruit.types.possible')) 

这是我应该做一个CROSS APPLY？

Answer 1

这是我应该做一个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)