PostgreSQL: implementing JSONB array aggregation and ordering in complex queries
Could someone explain This might be a silly question, but I tried several approaches but none seem to work... I'm working on a personal project and I'm working on a PostgreSQL 14 database where I need to aggregate JSONB data from multiple rows into a single array while maintaining a specific order. I have a table called `orders` with columns for `id`, `customer_id`, and `items`, where `items` is a JSONB array. My goal is to get a JSONB array of items ordered by the `created_at` timestamp from an `orders` table. Here's the query I've written: ```sql SELECT customer_id, jsonb_agg(items ORDER BY created_at) as aggregated_items FROM orders GROUP BY customer_id; ``` However, I'm working with the behavior: `behavior: ORDER BY expression must appear in target list`. This has me puzzled because `created_at` is not a column in the `SELECT` statement. I've tried moving the ordering logic inside the `jsonb_agg` function, but I still run into the same scenario. I also attempted to use a Common Table Expression (CTE) to prepare the data first, but it didn't resolve the behavior either. Here’s that approach: ```sql WITH sorted_orders AS ( SELECT customer_id, items, created_at FROM orders ORDER BY created_at ) SELECT customer_id, jsonb_agg(items) as aggregated_items FROM sorted_orders GROUP BY customer_id; ``` Again, I hit the same behavior. I really need to keep the data ordered as specified but need to seem to find the right way to achieve this without running into errors. Am I missing something in my query structure or logic? Any insights on how to correctly aggregate and order JSONB data in PostgreSQL would be greatly appreciated! My development environment is Ubuntu. Any ideas what could be causing this? The project is a application built with Sql. Any ideas what could be causing this?