Create json object and aggregate into json array in SqlServer

Question

I have following query in 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

In OUTER APPLY subquery I am creating json from columns I need and then aggregating those objects into json array. I need those two functions because sometimes I use only one of them. The same operation I would like to do in SqlServer. This is solution I managed so far:

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")

The problem is that SqlServer executing those two functions at once (this is purpose for FOR JSON PATH statement). So here are my questions:

1) Is there possible to create json object without wrapping it into array (oracle-like syntax)?

2) Is there possible to aggregate json objects into an array?

UPDATE I am using SqlServer version 2019 15.0.2000.5

Expected result (single record of products in json format)

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

Zhorov · Accepted Answer · 2020-04-09 07:22:58Z

4

If I understand the question correctly, the following statements are possible soltion (of course, they are based on the example data and statements in the question):

How to create a single JSON object:

If you want to generate one single JSON object, you need to use FOR JSON PATh for each row in the OUTER APPLY statement with the appropriate path expression. JSON_QUERY() is needed, because it returns a valid JSON and FOR JSON doesn't escape special characters.

Tables:

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)

Statement:

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")

Result:

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}}

How to aggregate JSON objects into an array:

By default FOR JSON creates a JSON array with one JSON object for each row. You only need to set a root key:

Statement:

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")

Result:

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

edited Apr 9, 2020 at 7:22

answered Apr 8, 2020 at 12:32

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adam Mrozek Over a year ago

First answer is great, but I tested second one, and this is not acceptable for me. I would like an array which contains ONLY aggregated rows like: [{"Id":"11111","Description":"Database Manager","Price":149.99},{"Id":"22222","Description":"Chassi","Price":249.99}] without 'Products' root. I found string_agg function which may be helpful but maybe is some other way to achieve that?

Adam Mrozek Over a year ago

This produces:

[{"Products":{"Product":{"Id":"AD071CD2-1F75-4DF5-8CC1-352DC01AB317","Description":"Rack","Price":1450.79000}}},{"Products":{"Product":{"Id":"8E5D1F1B-3932-463E-BACA-661DF5431E95","Description":"Database Manager","Price":149.99000}}}]

so this is even worse because there I have and array with array of products.

Zhorov Over a year ago

@AdamMrozek The second statement (from the How to aggregate JSON objects into an array part) returns the result from the answer. I'm able to get your unexpected result if I use FOR JSON PATH at the end of the first statement. Note, that SQL Server generates JSON output with FOR JSON, there are no functions LIKE JSON_ARRAYAGG and JSON_OBJECT. STRING_AGG will help if you choose a string-based approach for generating a valid JSON output. I'm not sure if you can use only one statement (as is with ORACLE).

Adam Mrozek Over a year ago

Ok, your answer was really helpful, I need string only to parse in c# in the next step. Thank you for your help :)

Swapnil Thube · Accepted Answer · 2022-11-15 10:17:01Z

0

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

answered Nov 15, 2022 at 10:17

Swapnil Thube

1331 silver badge5 bronze badges

Collectives™ on Stack Overflow

Create json object and aggregate into json array in SqlServer

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related