如何使 SQL 表的字段值成为 SQL 中 JSON 对象的键

Question

我一直在尝试以我想要的格式从我的数据库中获取 JSON 对象，所以我运行了以下 sql 查询：

SELECT PROJECTS.key_code AS CODE, PROJECTS.name AS Name,
         PROJECTS.date AS Date, PROJECTS.descr AS Description
         FROM PROJECTS LEFT JOIN ACCESS
         ON PROJECTS.key_code = ACCESS.key_code
         WHERE ACCESS.Ukey_code = '5d8hd5' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

查询结果如下：

{
  "Code": "h5P93G",
  "Name": "Project1 test name",
  "Date": "2017-09-03",
  "Description": "This is a test description 1"
 },
  "Code": "KYJ482",
  "Name": "Project2 test name",
  "Date": "2018-10-25",
  "Description": "This is a test description 2"
}

但实际上我想要的是不同的。JSON 对象应如下所示：

{
  "h5P93G": {
          "Name": "Project1 test name",
          "Date": "2017-09-03",
          "Description": "This is a test description 1"
        },
  "KYJ482": {
          "Name": "Project2 test name",
          "Date": "2018-10-25",
          "Description": "This is a test description 2"
        },
}

那么，我如何获取这个 JSON 对象？

Answer 1

据

我所知，您无法真正使用select ... for json的可变键名创建JSON。

但是，如果您不介意使用变量并且使用的是SQL Server 2017(否则不能将动态键用于json-modify(，则可以这样做：

declare @a nvarchar(max) = '{}'
select
    @a = json_modify(
        @a,
        concat('$."', p.key_code, '"'), /*This line fixed by @Zhorov*/
        json_query((select p.name, p.[date], p.descr for json path, without_array_wrapper))
    )
from projects as p
select @a

DB 小提琴演示

如果您使用的是早期版本的 SQL Server，则可以使用可以找到的任何聚合方法对其进行聚合(我在这里使用string_agg只是为了简单起见(：

select
    concat('{', string_agg(
        concat('"',p.key_code,'":',p.data),
        ','
    ), '}')
from (
    select
        p.key_code,
        (select p.name, p.[date], p.descr for json path, without_array_wrapper) as data
    from projects as p
) as p

DB 小提琴演示

您还可以考虑使用 string_escape 来防止在密钥可能包含特殊字符时出错：

select
    ...
        concat('"',string_escape(p.key_code,'json'),'":',p.data),
        ','
    ...

DB 小提琴演示