Créer un objet json et agréger dans un tableau json dans SqlServer

Question

J'ai la requête suivante dans 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

Sur OUTER APPLY Je crée un fichier json à partir des colonnes dont j'ai besoin, puis j'agrège ces objets dans un tableau json. J'ai besoin de ces deux fonctions car parfois je n'en utilise qu'une seule. J'aimerais effectuer la même opération dans SqlServer. Voici la solution que j'ai trouvée jusqu'à présent :

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

Le problème est que SqlServer exécute ces deux fonctions en même temps (c'est le but pour FOR JSON PATH ). Voici donc mes questions :

1) Est-il possible de créer un objet json sans le mettre dans un tableau (syntaxe de type oracle) ?

2) Est-il possible d'agréger des objets json dans un tableau ?

UPDATE J'utilise SqlServer version 2019 15.0.2000.5.

Résultat attendu (un seul enregistrement de produits au format json)

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

Answer 1

Si je comprends bien la question, les affirmations suivantes sont des solutions possibles (bien sûr, elles sont basées sur les données et les affirmations de l'exemple dans la question) :

Comment créer un objet JSON unique :

Si vous souhaitez générer un seul objet JSON, vous devez utiliser la méthode suivante FOR JSON PATh pour chaque ligne de la OUTER APPLY avec la déclaration appropriée path expression. JSON_QUERY() est nécessaire, car il renvoie un JSON valide et un FOR JSON n'échappe pas les caractères spéciaux.

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

Déclaration :

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

Résultat :

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

Comment agréger des objets JSON dans un tableau :

Par défaut FOR JSON crée un tableau JSON avec un objet JSON pour chaque ligne. Vous devez seulement définir une clé Root :

Déclaration :

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

Résultat :

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