0

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

all results

results wanted using WHERE clause in outer select with doors = 4 and color = blue

filtered

7
  • Are you trying to change what is in the generated JSON, or select rows from vendor_car_orders based on what is in the JSON? An example might help us understand what you are asking.
    – dwhitemv
    Oct 22, 2022 at 3:35
  • Note that your column doesn't have an alias. But even if it did, you can't reference a column alias from the SELECT list directly in the WHERE clause on the same level. You would need to wrap query in a derived table. But still: the JSON value will be an array, so you would need to use where cars -> 0 ->> color = 'blue' to check e.g. the first array element.
    – user1822
    Oct 22, 2022 at 6:14
  • Sample data and expected results would help immensely. Do you need a variable number of conditions, or will they always be exactly two? Will they always be on color and doors? Oct 23, 2022 at 11:22
  • i added my desired results, thanks!
    – luckyguy73
    Oct 24, 2022 at 0:08
  • Can you update the question and add the PostgreSQL version in use?
    – dwhitemv
    Oct 24, 2022 at 4:57

1 Answer 1

0
+100

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.

1
  • Pro tip: start your WHERE clause with WHERE 1 = 1, then you can dynamically add And... And... filters much more easily. Careful with OR operators, always enclose them in ( ) brackets and use 0=1 at the start instead.
    – Oreo
    Oct 27, 2022 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.