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

1yjd4xko 于 2022-11-19 发布在其他

关注(0)|答案(2)|浏览(293)

我在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对象而不将其 Package 到数组中（类似oracle的语法）？
2)有没有可能把json对象聚合到一个数组中？

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

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

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

JSON

来源：https://stackoverflow.com/questions/61099997/create-json-object-and-aggregate-into-json-array-in-sqlserver

2条答案

按热度按时间

brccelvz1#

如果我对问题的理解正确的话，下面的陈述是可能的解答（当然，它们是基于问题中的示例数据和陈述）：

如何创建单个JSON对象：

如果要生成一个JSON对象，则需要在OUTER APPLY语句中的每一行使用FOR JSON PATh和相应的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}]

赞(0）回复(0）举报 2022-11-19

6ovsh4lw2#

DECLARE @data varchar(max)
DECLARE @LIST NVARCHAR(MAX)
DECLARE @Temp TABLE (YourColumnName VARCHAR(MAX) NULL);
INSERT INTO @Temp SELECT DISTINCT columnName FROM YourTableName  WHERE(Id > 1000);
SELECT @LIST = STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(YourColumnName, 'N/A')), ',')  FROM @Temp

SET @data =(select @LIST as Name1,@LIST as Name2  For Json PATH)

select @data

赞(0）回复(0）举报 2022-11-19

我来回答

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

2条答案

相关问题

热门标签

最新问答