时间戳

首页 > 解决方案 > 从 SQL 表中选择 JSON 值并将结果转换为单个 JSON 对象

json - 从 SQL 表中选择 JSON 值并将结果转换为单个 JSON 对象

问题描述

我到处搜索，但似乎找不到任何像我正在尝试做的事情那样遥远的东西，但我确信这不是什么新鲜事，而且我很确定我有一个荷马时刻。

给定一个包含 JSON 列的表：

DECLARE @Table TABLE
(
    [VALUE] nvarchar(max) NOT NULL
);

列中的值可以是任何有效JSON OBJECT的，我无法控制数据，只有它是有效的，并且所有条目都具有相同的结构，但我不知道。这很重要，因为它确实限制了某些可用选项。

INSERT INTO @Table
VALUES ( N'{"X":"2020-01-01","Y":"27"}' )
, ( N'{"X":"2020-02-01","Y":"48"}' )
, ( N'{"X":"2020-04-01","Y":"63"}' )
, ( N'{"X":"2020-05-01","Y":"75"}' )
, ( N'{"X":"2020-06-01","Y":"32"}' )
, ( N'{"X":"2020-08-01","Y":"12"}' )
, ( N'{"X":"2020-09-01","Y":"96"}' )
, ( N'{"X":"2020-10-01","Y":"105"}' );

INSERT INTO @Table
VALUES ( N'{"Monkey":"1","Elephant":"9"}' )
, ( N'{"Monkey":"2","Elephant":"8"}' )
, ( N'{"Monkey":"3","Elephant":"7"}' )
, ( N'{"Monkey":"4","Elephant":"6"}' )
, ( N'{"Monkey":"5","Elephant":"5"}' )
, ( N'{"Monkey":"6","Elephant":"4"}' )
, ( N'{"Monkey":"7","Elephant":"3"}' )
, ( N'{"Monkey":"8","Elephant":"2"}' );

我需要能够将所有行的内容转换为单个JSON Document：

[{
    "VALUES": [
        { "X": "2020-01-01", "Y": "27" },
        { "X": "2020-02-01", "Y": "48" },
        { "X": "2020-04-01", "Y": "63" },
        { "X": "2020-05-01", "Y": "75" },
        { "X": "2020-06-01", "Y": "32" },
        { "X": "2020-08-01", "Y": "12" },
        { "X": "2020-09-01", "Y": "96" },
        { "X": "2020-10-01", "Y": "105" }
    ]
}]

鉴于我的SELECT陈述，我至少能够将数据查询到表结果中：

SELECT [VALUES] = JSON_QUERY( [VALUE], N'$' )
FROM @Table;

VALUES
{"X":"2020-01-01","Y":"27"}
{"X":"2020-02-01","Y":"48"}
{"X":"2020-04-01","Y":"63"}
{"X":"2020-05-01","Y":"75"}
{"X":"2020-06-01","Y":"32"}
{"X":"2020-08-01","Y":"12"}
{"X":"2020-09-01","Y":"96"}
{"X":"2020-10-01","Y":"105"}

但是当我将结果转换为JSON它时出错了：

SELECT [VALUES] = JSON_QUERY( [VALUE], N'$' )
FROM @Table
FOR JSON PATH;

[
    { "VALUES": { "X": "2020-01-01", "Y": "27" } },
    { "VALUES": { "X": "2020-02-01", "Y": "48" } },
    { "VALUES": { "X": "2020-04-01", "Y": "63" } },
    { "VALUES": { "X": "2020-05-01", "Y": "75" } },
    { "VALUES": { "X": "2020-06-01", "Y": "32" } },
    { "VALUES": { "X": "2020-08-01", "Y": "12" } },
    { "VALUES": { "X": "2020-09-01", "Y": "96" } },
    { "VALUES": { "X": "2020-10-01", "Y": "105" } }
]

对于我的生活，我似乎无法整理最后一点，希望另一组更聪明的眼睛会发现我的错误。

标签： jsonselectsql-server-2016

解决方案

如果您可以保证该表包含有效的 JSON，那么下面是一个快速而肮脏的解决方案。不使用任何 Microsoft SQL Server JSON 支持，而是使用一些字符串连接......

-- SQL SERVER 2016
select '[{ "VALUES":['
    +  stuff((  select ',' + t.[VALUE]
                from @Table t
                for xml path(''), type).value('.', 'nvarchar(max)'), 1,1,'')
    + ']}]';


-- SQL SERVER 2017 and later
select '[{ "VALUES":[' + string_agg(t.VALUE, ',') + ']}]' from @Table t;

第一组样本数据的格式化结果：

[
  {
    "VALUES": [
      {
        "X": "2020-01-01",
        "Y": "27"
      },
      {
        "X": "2020-02-01",
        "Y": "48"
      },
      {
        "X": "2020-04-01",
        "Y": "63"
      },
      {
        "X": "2020-05-01",
        "Y": "75"
      },
      {
        "X": "2020-06-01",
        "Y": "32"
      },
      {
        "X": "2020-08-01",
        "Y": "12"
      },
      {
        "X": "2020-09-01",
        "Y": "96"
      },
      {
        "X": "2020-10-01",
        "Y": "105"
      }
    ]
  }
]

推荐阅读