我有一个包含事务的JSON文件,每个事务包含多个项目。我需要使用T-SQL将数据扁平化到SQL Server表中。
我试过不同的选择来使它变平,但看起来我遗漏了一些东西。任何曾在类似结构上工作过的人有什么想法如何才能完成这一点?
DECLARE @json NVARCHAR(MAX);
SELECT @json = JsonPath
FROM [dbo].[stg_transactionsJson] b;
SELECT
CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.orderId')) AS orderId,
CONVERT(DATETIME, JSON_VALUE(c.Value, '$.openTime')) AS openTime,
CONVERT(DATETIME, JSON_VALUE(c.Value, '$.closeTime')) AS closeTime,
CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.operatorId')) AS operatorId,
CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.terminalId')) AS terminalId,
CONVERT(NVARCHAR(50), JSON_VALUE(c.Value, '$.sessionId')) AS sessionId,
CONVERT(NVARCHAR(50), JSON_VALUE(p.Value, '$.productGroupId')) AS productGroupId,
CONVERT(NVARCHAR(150), JSON_VALUE(p.Value, '$.productId')) AS productId,
CONVERT(NVARCHAR(50), JSON_VALUE(p.Value, '$.quantity')) AS quantity,
CONVERT(NVARCHAR(150), JSON_VALUE(p.Value, '$.taxValue')) AS taxValue,
CONVERT(NVARCHAR(50), JSON_VALUE(p.Value, '$.value')) AS ProductValue,
CONVERT(NVARCHAR(150), JSON_VALUE(p.Value, '$.priceBandId')) AS priceBandId,
GETDATE() AS DateUpdated
FROM
OPENJSON(@json) AS c
OUTER APPLY
OPENJSON(c.Value, '$."products"') AS p;
示例JSON如下所示
{
"orderId": 431,
"openTime": "2022-10-31T13:12:28",
"closeTime": "2022-10-31T13:12:32",
"operatorId": 7,
"terminalId": 4,
"sessionId": 1,
"products": [
{
"productId": 2632,
"productGroupId": 162,
"quantity": 1,
"taxValue": 0.58,
"value": 3.5,
"priceBandId": 2
},
{
"productId": 3224,
"productGroupId": 164,
"quantity": 1,
"taxValue": 0.08,
"value": 0.5,
"priceBandId": 2
}
],
"tenders": [
{
"tenderId": 2,
"value": 4.0
}
],
"type": 1,
"memberId": 1
}
2条答案
按热度按时间wnvonmuf1#
你可以这样做
首先在with子句中声明值,然后交叉应用乘积
| 订单ID|打开时间|关闭时间|操作员ID|终端ID|会话ID|产品组ID|产品ID|数量|税值|价值|价格范围ID|更新日期|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 四三一|二〇二二年十月三十一日|二〇二二年十月三十一日|七|四个|1个|一百六十二|小行星2632|1个|0.58|三、五|第二章|2023年1月11日13时57分47.607秒|
| 四三一|二〇二二年十月三十一日|二〇二二年十月三十一日|七|四个|1个|一百六十四|小行星3224|1个|0.08分|0.5分|第二章|2023年1月11日13时57分47.607秒|
fiddle
6qftjkof2#
预期输出定义了确切的语句,但如果需要解析嵌套的JSON内容,可以尝试以下组合:
OPENJSON()
,带有显式模式和AS JSON
修饰符,用于嵌套JSONAPPLY
运算符。T-SQL: