gpt4 book ai didi

json - 使用 T-SQL 将 JSON 转换为表

转载 作者:行者123 更新时间:2023-12-05 08:51:56 25 4
gpt4 key购买 nike

这是JSON输入

{
"agentID":"WJ",
"recordType":"AHL",
"recordReference":{
"stationCode":"ATL",
"airlineCode":"XS",
"recordId":"10001"
},
"entries":[
{
"bag":{
"bagType":"22",
"bagSize":"S",
"category":"1"
},
"seqNo":"1",
"noOfBagsGiven":"2"
},
{
"bag":{
"bagType":"23",
"bagSize":"L",
"category":"1"
},
"seqNo":"2",
"noOfBagsGiven":"5",
"dateBagsGiven":"2019-09-18"
}
]
}

这是我希望得到的输出(表格或去限制) enter image description here

我不知道如何遍历子条目,这是我目前所知道的

DECLARE @JSON    VARCHAR(MAX) =
'{
"agentID":"WJ",
"recordType":"AHL",
"recordReference":{
"stationCode":"ATL",
"airlineCode":"XS",
"recordId":"10001"
},
"entries":[
{
"bag":{
"bagType":"22",
"bagSize":"S",
"category":"1"
},
"seqNo":"1",
"noOfBagsGiven":"2"
},
{
"bag":{
"bagType":"23",
"bagSize":"L",
"category":"1"
},
"seqNo":"2",
"noOfBagsGiven":"5",
"dateBagsGiven":"2019-09-18"
}
]
}';

SELECT *
FROM OPENJSON(@json) WITH(agent_id VARCHAR(2) '$.agentID'
,record_type VARCHAR(4) '$.recordType'
,station_code VARCHAR(4) '$.recordReference.stationCode'
,airline_code VARCHAR(4) '$.recordReference.airlineCode'
,record_id INT '$.recordReference.recordId'
,bag_type INT '$.entries.bag.bagType'
,bag_size VARCHAR(2) '$.entries.bag.bagSize'
,bag_category INT '$.entries.bag.bagCategory'
,date_bags_given DATE '$.entries.bag.dateBagsGiven'
);

最佳答案

另一种可能的方法是使用 JSON_VALUE()(从 JSON 字符串中提取标量值)和 OPENJSON()(到解析 JSON 字符串并以表格形式获取结果):

SELECT 
JSON_VALUE(@JSON,'$.agentID') AS agentID,
JSON_VALUE(@JSON,'$.recordType') AS recordType,
JSON_VALUE(@JSON,'$.recordReference.stationCode') AS stationCode,
JSON_VALUE(@JSON,'$.recordReference.airlineCode') AS airlineCode,
JSON_VALUE(@JSON,'$.recordReference.recordId') AS recordId,
j.*
FROM OPENJSON(@JSON, '$.entries') WITH (
bagType VARCHAR(10) '$.bag.bagType',
bagSize VARCHAR(10) '$.bag.bagSize',
category VARCHAR(10) '$.bag.category',
seqNo VARCHAR(10) '$.seqNo',
noOfBagsGiven VARCHAR(10) '$.noOfBagsGiven',
dateBagsGiven VARCHAR(10) '$.dateBagsGiven'
) AS j

关于json - 使用 T-SQL 将 JSON 转换为表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58067921/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com