I am trying to create “Json Duality View” to generate the following json structure with sample mocked data. I am getting error from the following code:
Error: “multiple tables error Restructure the SQL statement to specify only one table in the FROM clause”.
I need help to fix this error and suggestions to make it optimum. Important requirement is , total order value, order count for each customer need to be derived at run time.
Data Model:
- country_master table stores countries and their code (primary key: country_id)
- customer_type_master - this table stores different type of customers for each country code. (primary key: country_id, customer_type,customer_id)
- order_master - this table stores order details for each customer id (primary key: order_id,)
I need to generate this json structure:
{
"_id":USA,
"customers":[ {"customerId: 123, "totalOrderValue": 5000, "totalOrderCount" : 10}
]
}
Here is the Json Duality View code:
CREATE OR REPLACE JSON DUALITY VIEW CUSTOMER_ORDERS AS
SELECT JSON_OBJECT(
'_id' VALUE pm.country_id,
'customers' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'customerId' VALUE em.customer_id,
'totalOrderValue' VALUE sum(am.order_value),
totalOrderCount VALUE count(\*)
)
)
FROM customer_type_master em,
order_master am
WHERE em.country_id = pm.country_id
AND em.customer_type = 'MIDDLE_CLASS'
and am.customerId=em.customer_id
group by em.country_id, em.customer_id WITH CHECK OPTION
)
)
FROM country_master pm;