选择使用T-SQL格式化为报表格式的Json-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

选择使用T-SQL格式化为报表格式的Json

2020-01-04 14:57:43 207 1

我将JSON以以下格式存储在SQL Server数据库表中。我已经能够捏造一种获得所需值的方法,但是觉得必须有一种使用T-SQL的更好的方法。JSON以以下格式从报告中输出,其中“列”中的列名称对应于“行”-“数据”数组值。

因此,“会计月份”列对应于数据值“ 11”,“会计年度”对应于“ 2019”,依此类推。

{
  "report": "Property ETL",
  "id": 2648,
  "columns": [
    {
      "name": "Fiscal Month",
      "dataType": "int"
    },
    {
      "name": "Fiscal Year",
      "dataType": "int"
    },
    {
      "name": "Portfolio",
      "dataType": "varchar(50)"
    },
    {
      "name": "Rent",
      "dataType": "int"
    }
],
"rows": [
    {
      "rowName": "1",
      "type": "Detail",
      "data": [
        11,
        2019,
        "West Group",
        10
      ]
    },
    {
      "rowName": "2",
      "type": "Detail",
      "data": [
        11,
        2019,
        "East Group",
        10
      ]
    },
    {
      "rowName": "3",
      "type": "Detail",
      "data": [
        11,
        2019,
        "East Group",
        10
      ]
    },
    {
      "rowName": "Totals: ",
      "type": "Total",
      "data": [
        null,
        null,
        null,
        30
      ]
    }
  ]
}

为了获得“数据”数组中的数据,我目前在T-SQL中有一个两步过程,在该过程中,我创建了一个临时表,然后从其中的“ $ .Rows”插入行键/值。然后,我可以为每一行选择单独的列

CREATE TABLE #TempData 
(
    Id INT,
    JsonData VARCHAR(MAX)
)

DECLARE @json VARCHAR(MAX);
DECLARE @LineageKey INT;

SET @json = (SELECT JsonString FROM Stage.Report);
SET @LineageKey = (SELECT LineageKey FROM Stage.Report);    

INSERT INTO #TempData(Id, JsonData)
    (SELECT [key], value FROM OPENJSON(@json, '$.rows'))

MERGE [dbo].[DestinationTable] TARGET
USING 
(
    SELECT 
        JSON_VALUE(JsonData, '$.data[0]') AS FiscalMonth,
        JSON_VALUE(JsonData, '$.data[1]') AS FiscalYear,
        JSON_VALUE(JsonData, '$.data[2]') AS Portfolio,
        JSON_VALUE(JsonData, '$.data[3]') AS Rent
     FROM #TempData
     WHERE JSON_VALUE(JsonData, '$.data[0]') is not null
) AS SOURCE     
... 
etc., etc.

这可行,但是我想知道是否有一种直接选择数据值的方法,而无需将其放入临时表的中间步骤。我阅读的文档和示例似乎都要求数据具有与之关联的名称才能访问它。当我尝试通过索引直接在某个位置访问数据时,我只会得到Null。

取消 提交回答
全部回答(1)
  • 祖安文状元
    2020-01-04 14:59:17

    是的,有可能没有临时表:

    DECLARE @json NVARCHAR(MAX) = 
    N'
    {
      "report": "Property ETL",
      "id": 2648,
      "columns": [
        {
          "name": "Fiscal Month",
          "dataType": "int"
        },
        {
          "name": "Fiscal Year",
          "dataType": "int"
        },
        {
          "name": "Portfolio",
          "dataType": "varchar(50)"
        },
        {
          "name": "Rent",
          "dataType": "int"
        }
    ],
    "rows": [
        {
          "rowName": "1",
          "type": "Detail",
          "data": [
            11,
            2019,
            "West Group",
            10
          ]
        },
        {
          "rowName": "2",
          "type": "Detail",
          "data": [
            11,
            2019,
            "East Group",
            10
          ]
        },
        {
          "rowName": "3",
          "type": "Detail",
          "data": [
            11,
            2019,
            "East Group",
            10
          ]
        },
        {
          "rowName": "Totals: ",
          "type": "Total",
          "data": [
            null,
            null,
            null,
            30
          ]
        }
      ]
    }    
    }';
    
    

    并查询:

    SELECT s.value,
      rowName = JSON_VALUE(s.value, '$.rowName'),
      [type] = JSON_VALUE(s.value, '$.type'),
      s2.[key],
      s2.value
    FROM OPENJSON(JSON_QUERY(@json, '$.rows')) s
    CROSS APPLY OPENJSON(JSON_QUERY(s.value, '$.data')) s2;
    db <> fiddle演示
    
    

    或作为每个细节的一行:

    SELECT s.value,
      rowName = JSON_VALUE(s.value, '$.rowName'),
      [type] = JSON_VALUE(s.value, '$.type'),
      JSON_VALUE(s.value, '$.data[0]') AS FiscalMonth,
      JSON_VALUE(s.value, '$.data[1]') AS FiscalYear,
      JSON_VALUE(s.value, '$.data[2]') AS Portfolio,
      JSON_VALUE(s.value, '$.data[3]') AS Rent
    FROM OPENJSON(JSON_QUERY(@json, '$.rows')) s;
    
    0 0
相关问答

18

回答

【大咖问答】对话PostgreSQL 中国社区发起人之一,阿里云数据库高级专家 德哥

阿里ACE 彭飞 2019-07-10 09:36:10 1039890浏览量 回答数 18

145

回答

【新手入门】云服务器linux使用手册

fanyue88888 2012-11-26 17:14:18 157701浏览量 回答数 145

8

回答

OceanBase 使用动画(持续更新)

mq4096 2019-02-20 17:16:36 337017浏览量 回答数 8

110

回答

OSS存储服务-客户端工具

newegg11 2012-05-17 15:37:18 295559浏览量 回答数 110

22

回答

爬虫数据管理【问答合集】

我是管理员 2018-08-10 16:37:41 147243浏览量 回答数 22

38

回答

安全组详解,新手必看教程

我的中国 2017-11-30 15:23:46 259843浏览量 回答数 38

82

回答

OSS入门教程

belle.zhoux 2014-07-07 17:14:27 151465浏览量 回答数 82

21

回答

请教一下数据量有100万条左右要什么配置?

易网网络 2013-03-27 15:18:02 192640浏览量 回答数 21

24

回答

【精品问答】python技术1000问(1)

问问小秘 2019-11-15 13:25:00 475695浏览量 回答数 24

2

回答

区域选择帮助

fanyue88888 2012-12-07 15:54:30 204394浏览量 回答数 2
0
文章
1898
问答
问答排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载