Comment générer JSON via SQL Pl / JSON

declare

   v_customers pljson_list := pljson_list();
   v_customer  pljson;
   v_products  pljson_list;

begin

   for c in (select distinct customer_id, customer_name
               from customer
              where customer_id = 1) loop

      v_customer := pljson();
      v_customer.put('customer_id', c.customer_id);
      v_customer.put('customer_name', c.customer_name);

      v_products := json_dyn.executeList('select distinct product_id, product_name
                                            from sales
                                           where customer_id = ' || c.customer_id);
      v_customer.put('products', v_products.to_json_value);

      v_customers.append(v_customer.to_json_value);

   end loop;

end;
In this snippet I used the pljson types (latest version found at https://github.com/pljson/pljson): if you are using an old version of the library, replacing any occurrence of "pljson" with "json" should be enough (but I recommend upgrading or you might have problems on Oracle 18 or newer).

@ararMimede ainsi, une liste dans la liste

{
    "customer_id": 1,
    "customer_name": "Customer A",
    "product": [
        {
            "product_id": 5715,
            "product_name": "Product A",
            "list" : [
                {
                    "a" : 1,
                    "b": 2
                },
                {
                    "a" : 10,
                    "b" : 20
                }
            ]
        }
    ]
}

Je suppose que la "liste" provient d'une table: xxx

hth.