Postgresql SELECT ORDER BY Column generated by WITH
SELECT query on Postgre that contain
WITH subquery as json column. I need to order query result based on
WITH subquery json data or column.
This is a sample query that I ran:
SELECT person.id, ( WITH person_warehouse AS ( SELECT count(warehouse.id) AS total_warehouse, sum((warehouse.detailinfo -> 'total_area'::text)::numeric) AS total_area_storage FROM warehouse WHERE warehouse.owner_id = person.id GROUP BY warehouse.owner_id ) SELECT row_to_json(person_warehouse .*) AS json_tree FROM person_warehouse) AS warehousedata, FROM person ORDER BY warehousedata->>'total_warehouse' DESC
But it is return that warehousedata is not a column. How can I sort select query based on subquery result?
You can reference an alias in the
ORDER BYclause, but you cannot use that alias in an expression. You'd have to repeat the expression from the
This query is written in a spectacularly complicated fashion, it could be written much simpler:
SELECT person.id, json_build_object( 'total_warehouse', count(warehouse.id), 'total_area_storage', sum((warehouse.detailinfo ->> 'total_area')::numeric) ) AS warehousedata FROM person LEFT JOIN warehouse ON warehouse.owner_id = person.id GROUP BY person_id ORDER BY count(warehouse.id) DESC;
If you really want the
ORDER BYclause sorted as a string, throw in a type cast.