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
}

Answer 1

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