将嵌套的JSON数据插入SQL

b1payxdu 于 2023-10-21 发布在其他

关注(0)|答案(1)|浏览(102)

我一直在处理将Xero Report数据导入数据仓库的问题。Xero生成数据的方式很烦人，而且嵌套很深。

{
  "Id": "2af62c5f-dc79-4b6d-9658-bd94446e3c01",
  "Status": "OK",
  "ProviderName": "Data Warehouse",
  "DateTimeUTC": "/Date(1626326365968)/",
  "Reports": [
    {
      "ReportID": "ProfitAndLoss",
      "ReportName": "Profit and Loss",
      "ReportType": "ProfitAndLoss",
      "ReportTitles": [
        "Profit and Loss",
        "ACME",
        "1 July 2021 to 31 July 2021"
      ],
      "ReportDate": "15 July 2021",
      "UpdatedDateUTC": "/Date(1626326365968)/",
      "Fields": [],
      "Rows": [
        {
          "RowType": "Header",
          "Cells": [
            {
              "Value": ""
            },
            {
              "Value": "31 Jul 21"
            }
          ]
        },
        {
          "RowType": "Section",
          "Title": "Less Cost of Sales",
          "Rows": [
            {
              "RowType": "Row",
              "Cells": [
                {
                  "Value": "EPAY",
                  "Attributes": [
                    {
                      "Value": "10f71a2c-afa9-4005-824b-38c722896e44",
                      "Id": "account"
                    }
                  ]
                },
                {
                  "Value": "32556.90",
                  "Attributes": [
                    {
                      "Value": "10f71a2c-afa9-4005-824b-38c722896e44",
                      "Id": "account"
                    }
                  ]
                }
              ]
            },
            {
              "RowType": "Row",
              "Cells": [
                {
                  "Value": "Purchases",
                  "Attributes": [
                    {
                      "Value": "b4dd09f7-37e6-4ac5-87a0-61d11b5cd4c0",
                      "Id": "account"
                    }
                  ]
                },
                {
                  "Value": "1135980.98",
                  "Attributes": [
                    {
                      "Value": "b4dd09f7-37e6-4ac5-87a0-61d11b5cd4c0",
                      "Id": "account"
                    }
                  ]
                }
              ]
            }
        }
    }
}

此JSON是直接REPORT API JSON的示例。
我一直在使用SQL语句来摄取它，到目前为止，我有这个，

DECLARE @json NVARCHAR(1000)
SELECT @json = 
N'
<JSON here<'     
SELECT
        JSON_Value (c.value, '$.Reports.ReportID') as ReportID,
        JSON_Value (c.value, '$.Reports.ReportDate') as ReportDate,
        JSON_Value (c.value, '$.Reports.Report') as ReportTitle,
        JSON_Value (p.value, '$.Reports.Rows.RowType') as RowType, 
        JSON_Value (p.value, '$.Reports.Rows.RowType.Row.Cells.Value') as ReportValue
      
    FROM OPENJSON (@json, '$.Reports.ReportID') as c
    CROSS APPLY OPENJSON (c.value, '$.Reports.Rows.RowType') as p

我现在撞到了一堵墙，不知道从哪里开始。希望得到一些建议来帮助我进入下一步。

JSON

来源：https://stackoverflow.com/questions/68437370/insert-nested-json-data-into-sql

1条答案

按热度按时间

sbtkgmzw1#

我快速地摆弄了一下，得出了这个结论：

Select  * FROM OPENJSON((@json))  WITH 
        (
            
            
            Reports NVARCHAR(MAX)'$.Reports' AS JSON
           

        ) AS JsonProccessingData
        CROSS APPLY OPENJSON (Reports) WITH
        (
            ReportID VARCHAR(500) '$.ReportID',
            ReportDate VARCHAR(200) '$.ReportDate',
            ReportTitle VARCHAR(200) '$.ReportName',
            Rows NVARCHAR(MAX)'$.Rows' AS JSON
        )
        CROSS APPLY OPENJSON (Rows) WITH
        (
            RowType VARCHAR(500) '$.RowType',
            Cells NVARCHAR(MAX)'$.Cells' AS JSON
        )
        CROSS APPLY OPENJSON (Cells) WITH
        (
            ReportValue NVARCHAR(MAX)'$.Value' AS JSON
        )

不知道如何处理多个值，希望这是有点帮助？

赞(0）回复(0）举报 2023-10-21

我来回答

将嵌套的JSON数据插入SQL

1条答案

相关问题

热门标签

最新问答