The way that I am building this query dynamically is that everything is static that is located before the last
WHERE clause. The
ORDER BY clauses can be created and appended based on what query params are sent over.
SELECT vco.id, vco.branch, ( SELECT json_agg(x) as cars from ( SELECT * FROM car_info WHERE branch = vco.branch ) as x ) FROM vendor_car_orders vco -- added dynamically based on query params WHERE cars->>'color' = 'blue' AND cars->>'doors' = 4 ORDER BY 1;
results without any filter
results wanted using
WHERE clause in outer select with doors = 4 and color = blue