英文:
Insert Json data into temp table using SQL Server Json
问题
I try to insert below json in temp table,
{
"offers": {
"burstableenablement": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement1": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement2": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
}
}
}
Table:
the table should be like
burstableenablement | australia-central | 35.635 | WebDirect
burstableenablement | australia-central-2 | 35.635 | WebDirect
burstable-enablement1 | australia-central | 35.635 | WebDirect
burstable-enablement1 | australia-central-2 | 35.635 | WebDirect
the above table shows like in temp, I don't know how to achieve that, any solution is helpful for me and thanks in advance.
英文:
I try to insert below json in temp table,
{
"offers": {
"burstableenablement": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement1": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement2": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
}
}
}
Table:
the table should be like
burstableenablement | australia-central | 35.635 | WebDirect
burstableenablement | australia-central-2 | 35.635 | WebDirect
burstable-enablement1 | australia-central | 35.635 | WebDirect
burstable-enablement1 | australia-central-2 | 35.635 | WebDirect
the above table show like in temp, i don't know how to achieve that, any solution it helpful for me and thanks in advance.
答案1
得分: 0
以下是您提供的SQL查询的翻译：
选择 x.[key]，y.[key]，json_value(y.value，'$.value') 作为 value，json_value(y.value，'$.pricingType')
从 openjson(N'{
"offers": {
"burstableenablement": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement1": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement2": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
}
}
}', '$.offers') x
交叉应用 openjson(value，'$.prices') y
英文:
Something like this perhaps:
select x.[key], y.[key], json_value(y.value, '$.value') AS value, json_value(y.value, '$.pricingType')
from openjson(N'{
"offers": {
"burstableenablement": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement1": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
},
"burstable-enablement2": {
"prices": {
"australia-central": {
"value": 35.635,
"pricingType": "WebDirect"
},
"australia-central-2": {
"value": 35.635,
"pricingType": "WebDirect"
}
}
}
}
}', '$.offers') x
cross apply openjson(value, '$.prices') y
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论