因此,我有一个任务来提取这种嵌套的JSON,但由于我从来没有做过这样的事情,我被卡住了,我不能把我的头围绕在CROSS APPLY函数:$
SQL Server 2019(RTM版)-版本号:15.0.2000.5(X64)
{
"id": 1,
"name": "Buzz",
"tagline": "A Real Bitter Experience.",
"first_brewed": "09/2007",
"description": "A light, crisp and bitter IPA brewed with English and American hops. A small batch brewed only once.",
"image_url": "https://images.punkapi.com/v2/keg.png",
"abv": 4.5,
"ibu": 60,
"target_fg": 1010,
"target_og": 1044,
"ebc": 20,
"srm": 10,
"ph": 4.4,
"attenuation_level": 75,
"volume": {
"value": 20,
"unit": "litres"
},
"boil_volume": {
"value": 25,
"unit": "litres"
},
"method": {
"mash_temp": [
{
"temp": {
"value": 64,
"unit": "celsius"
},
"duration": 75
}
],
"fermentation": {
"temp": {
"value": 19,
"unit": "celsius"
}
},
"twist": null
},
"ingredients": {
"malt": [
{
"name": "Maris Otter Extra Pale",
"amount": {
"value": 3.3,
"unit": "kilograms"
}
},
{
"name": "Caramalt",
"amount": {
"value": 0.2,
"unit": "kilograms"
}
},
{
"name": "Munich",
"amount": {
"value": 0.4,
"unit": "kilograms"
}
}
],
"hops": [
{
"name": "Fuggles",
"amount": {
"value": 25,
"unit": "grams"
},
"add": "start",
"attribute": "bitter"
},
{
"name": "First Gold",
"amount": {
"value": 25,
"unit": "grams"
},
"add": "start",
"attribute": "bitter"
},
{
"name": "Fuggles",
"amount": {
"value": 37.5,
"unit": "grams"
},
"add": "middle",
"attribute": "flavour"
},
{
"name": "First Gold",
"amount": {
"value": 37.5,
"unit": "grams"
},
"add": "middle",
"attribute": "flavour"
},
{
"name": "Cascade",
"amount": {
"value": 37.5,
"unit": "grams"
},
"add": "end",
"attribute": "flavour"
}
],
我已经尝试过为mash_temp执行此操作,但老实说,我不知道我在做什么,并且确实需要一些指导
select *
from openjson(@json, '$.method')
with (
mash_temp nvarchar(max) '$.mash_temp' as json
) j
cross apply openjson(j.mash_temp)
with (
temp nvarchar(max) '$.temp' as json
) j2
cross apply openjson(j2.temp)
with (
value smallint '$.value',
unit varchar(20) '$.unit'
) j3
1条答案
按热度按时间8i9zcol21#
有关OpenJson的Microsoft文档非常充分:https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16。
处理子对象有点困难。下面是一个如何将mash_temp放入表中的快速示例。我在这篇文章中使用了这个解决方案:是的。
| 持续时间|临时工|临时值|温度单位|
| - -|- -|- -|- -|
| 七十五|{1} “值”:64, “单位”:“摄氏度” 上|六十四|摄氏度|
fiddle