将嵌套JSON数据拼合到SQL Server表中

3qpi33ja 于 2023-01-12 发布在 SQL Server

关注(0)|答案(2)|浏览(144)

我有一个包含事务的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
}

sql-server

来源：https://stackoverflow.com/questions/75083492/flatten-nested-json-data-into-sql-server-tables

2条答案

按热度按时间

wnvonmuf1#

你可以这样做
首先在with子句中声明值，然后交叉应用乘积

DECLARE @json NVARCHAR(MAX);
SET @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
}
';
SELECT c.orderId
  ,c.openTime
  ,c.closeTime
  ,c.operatorId
  ,c.terminalId
  ,c.sessionId
  , JSON_VALUE(p.Value, '$.productGroupId') productGroupId
  , JSON_VALUE(p.Value, '$.productId') productId
  , JSON_VALUE(p.Value, '$.quantity') quantity
  , JSON_VALUE(p.Value, '$.taxValue') taxValue
  , JSON_VALUE(p.Value, '$.value') value
  , JSON_VALUE(p.Value, '$.priceBandId') priceBandId
  ,GETDATE() AS DateUpdated
  FROM
  OPENJSON(@json)  WITH (
  orderId int '$.orderId',
  openTime date '$.openTime',
  closeTime date '$.closeTime',
  operatorId int '$.operatorId',
  terminalId int '$.terminalId',
  sessionId int '$.sessionId',
  [products] NVARCHAR(MAX) as JSON) c
CROSS APPLY OPENJSON(c.products) p

| 订单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

赞(0）回复(0）举报 2023-01-12

6qftjkof2#

预期输出定义了确切的语句，但如果需要解析嵌套的JSON内容，可以尝试以下组合：

OPENJSON()，带有显式模式和AS JSON修饰符，用于嵌套JSON
每个嵌套级别的附加APPLY运算符。

T-SQL：

SELECT
   j1.orderId, j1.openTime, j1.closeTime, j1.operatorId, j1.terminalId, j1.sessionId, j1.type, j1.memberId,
   j2.productGroupId, j2.productId, j2.quantity, j2.taxValue, j2.productValue, j2.priceBandId,
   j3.tenderId, j3.tenderValue
FROM stg_transactionsJson s
OUTER APPLY OPENJSON(s.stg_transactionsJson) WITH (
   orderId NVARCHAR(50) '$.orderId',
   openTime DATETIME '$.openTime',
   closeTime DATETIME '$.closeTime',
   operatorId NVARCHAR(50) '$.operatorId',
   terminalId NVARCHAR(50) '$.terminalId',
   sessionId NVARCHAR(50) '$.sessionId',
   products NVARCHAR(MAX) '$.products' AS JSON,
   tenders NVARCHAR(MAX) '$.tenders' AS JSON,
   type int '$.type',
   memberId int '$.memberId'
) j1
OUTER APPLY OPENJSON(j1.products) WITH (
   productGroupId NVARCHAR(50) '$.productGroupId',
   productId NVARCHAR(150) '$.productId',
   quantity NVARCHAR(50) '$.quantity',
   taxValue NVARCHAR(150) '$.taxValue',
   productValue NVARCHAR(50) '$.value',
   priceBandId NVARCHAR(150)'$.priceBandId'
) j2
OUTER APPLY OPENJSON(j1.tenders) WITH (
   tenderId NVARCHAR(150) '$.tenderId',
   tenderValue NVARCHAR(50) '$.value'
) j3

赞(0）回复(0）举报 2023-01-12

我来回答

将嵌套JSON数据拼合到SQL Server表中

2条答案

相关问题

热门标签

最新问答