I'm trying to generate a JSON from an SQL with PL/JSON
declare
customer json_list := json_list();
product json_list;
begin
customer:= json_dyn.executeList('SELECT DISTINCT
A.customer_id,
A.customer_name,
FROM customer A
WHERE A.customer_id = 1');
product := json_dyn.executeList('SELECT DISTINCT
A.product_id,
A.product_name,
FROM sales A
INNER JOIN customer B
ON A.customer_id = B.customer_id
WHERE A.customer_id = 1');
end;
What I need is to join these two select to become a single JSON and look like this: In a way where product is a sales key and the value of product is a list of products
[
{
"customer_id": 1,
"customer_name": "Customer A",
"product": [
{
"product_id": 5715,
"product_name": "Product A",
},
{
"product_id": 7841,
"product_name": "Product B",
}
]
}
]
does anyone know how to do it that way?
I'm supposing the "list" come from some table:
declare
v_customers pljson_list := pljson_list();
v_products pljson_list;
v_lists pljson_list;
v_customer pljson;
v_product pljson;
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 := pljson_list();
for p in (select distinct product_id, product_name
from sales
where customer_id = c.customer_id) loop
v_product := pljson();
v_product.put('product_id', p.product_id);
v_product.put('product_name', p.product_name);
v_lists := json_dyn.executeList('select distinct a, b
from lists
where product_id = ' || p.product_id);
v_product.put('list', v_lists.to_json_value);
v_products.append(v_product.to_json_value);
end loop;
v_customer.put('products', v_products.to_json_value);
v_customers.append(v_customer.to_json_value);
end loop;
end;
HTH.