Can I do a WHERE clause to filter a json_agg column in the outer query?

Question

The way that I am building this query dynamically is that everything is static that is located before the last WHERE clause. The WHERE and 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

Answer 1

Taking out the subqueries makes the job much easier:

SELECT vco.id, vco.branch, json_agg(car_info) as cars -- Static
FROM car_info                                         -- Static
JOIN vendor_car_orders vco USING (branch)             -- Static
WHERE color = 'blue' AND doors = 4                    -- Conditions go here
GROUP BY vco.id, vco.branch                           -- Static
ORDER BY vco.id                                       -- Optional ordering goes here

Easy to compose the query in client code or as Dynamic SQL in PL/pgSQL. The call to json_agg(car_info) is a bit of PostgreSQL magic, where a table implicitly defines a matching composite type. If there are ever columns in car_info that shouldn't appear in the JSON then use a view to hide them.