时间戳

首页 > 解决方案 > 在SqlServer中创建json对象并聚合成json数组

json - 在SqlServer中创建json对象并聚合成json数组

问题描述

我在 ORACLE 中有以下查询：

SELECT *
FROM "Supplier" s
OUTER APPLY( 
    SELECT JSON_ARRAYAGG(JSON_OBJECT(p."Id", p."Description", p."Price")) as "Products"
    FROM "Products" p
    WHERE p."SupplierId" = s."Id"
) sp

在OUTER APPLY子查询中，我从我需要的列创建 json，然后将这些对象聚合到 json 数组中。我需要这两个功能，因为有时我只使用其中一个。我想在 SqlServer 中执行相同的操作。这是我迄今为止管理的解决方案：

SELECT *
FROM "Supplier" as s
OUTER APPLY(
    SELECT p."Id", p."Description", p."Price"
    FROM "Products" as p
    WHERE p."SupplierId" = s."Id"
    FOR JSON PATH
) as sp("Products")

问题是 SqlServer 一次执行这两个函数（这是FOR JSON PATH语句的目的）。所以这是我的问题：

1）是否可以创建 json 对象而不将其包装到数组中（类似 oracle 的语法）？

2) 是否可以将 json 对象聚合成一个数组？

更新 我正在使用 SqlServer 版本 2019 15.0.2000.5

预期结果（json格式的产品单条记录）

"Products":{
    "Id":"FEB0646B709B45B5A306E10599716F28",
    "Description":"Database Manager",
    "Price":149.99
}

标签： jsonsql-server

解决方案

如果我正确理解了这个问题，以下陈述是可能的解决方案（当然，它们是基于问题中的示例数据和陈述）：

如何创建单个 JSON 对象：

如果要生成单个 JSON 对象，则需要为语句FOR JSON PATh中的每一行OUTER APPLY使用适当的path表达式。JSON_QUERY()需要，因为它返回一个有效的 JSON 并且FOR JSON不转义特殊字符。

表：

CREATE TABLE Supplier (
   Id int,
   Description varchar(50),
   DateStart date
)
CREATE TABLE Products (
   Id varchar(5),
   SupplierId int,
   Description varchar(100),
   Price numeric(10, 2)
)
INSERT INTO Supplier (Id, Description, DateStart)
VALUES (1, 'Oracle', '19900505')
INSERT INTO Products (Id, SupplierId, Description, Price)
VALUES ('11111', 1, 'Database Manager', 149.99)
INSERT INTO Products (Id, SupplierId, Description, Price)
VALUES ('22222', 1, 'Chassi', 249.99)

陈述：

SELECT *
FROM "Supplier" s
OUTER APPLY(
    SELECT Products = JSON_QUERY((
       SELECT 
          p."Id" AS 'Product.Id', 
          p."Description" AS 'Product.Description', 
          p."Price" AS 'Product.Price'
       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ))
    FROM "Products" as p
    WHERE p."SupplierId" = s."Id"
) sp ("Products")

结果：

Id  Description DateStart   Products
1   Oracle      1990-05-05  {"Product":{"Id":"11111","Description":"Database Manager","Price":149.99}}
1   Oracle      1990-05-05  {"Product":{"Id":"22222","Description":"Chassi","Price":249.99}}

如何将 JSON 对象聚合到数组中：

默认情况下FOR JSON，创建一个 JSON 数组，其中每一行都有一个 JSON 对象。您只需要设置一个根密钥：

陈述：

SELECT *
FROM "Supplier" s
OUTER APPLY(
    SELECT p."Id", p."Description", p."Price"
    FROM "Products" p
    WHERE p."SupplierId" = s."Id"
    FOR JSON PATH
) sp("Products")

结果：

Id  Description DateStart   Products
1   Oracle      1990-05-05  [{"Id":"11111","Description":"Database Manager","Price":149.99},{"Id":"22222","Description":"Chassi","Price":249.99}]

推荐阅读