PostgreSQL: Unexpected Results with JSONB Array in Aggregation Query
I'm testing a new approach and I'm building a feature where I need some guidance on Could someone explain I've been banging my head against this for hours... I've searched everywhere and can't find a clear answer... I'm working with an scenario when trying to aggregate JSONB arrays in PostgreSQL. I'm using PostgreSQL 13.3, and I have a table named `orders` with a column `items` that stores an array of JSONB objects. My goal is to count the distinct item IDs from these JSONB arrays for each order, but I'm getting unexpected results. Here's the query I've tried: ```sql SELECT order_id, COUNT(DISTINCT (item->>'id')) AS distinct_item_count FROM orders, jsonb_array_elements(items) AS item GROUP BY order_id; ``` This query should give me the distinct count of item IDs for each order, but instead, I am getting a count that seems to include duplicates. For example, one order has items with IDs ["1", "2", "1", "3"], and I expect the count to be 3, but it returns 4. I've also tried wrapping the `item->>'id'` in a `DISTINCT` clause like this: ```sql SELECT order_id, COUNT(DISTINCT DISTINCT (item->>'id')) AS distinct_item_count FROM orders, jsonb_array_elements(items) AS item GROUP BY order_id; ``` However, that just resulted in a syntax behavior. I’ve checked the contents of the `items` column and confirmed there are no empty entries. Why am I seeing these unexpected counts, and how can I accurately aggregate the distinct item IDs? Any help would be appreciated! For context: I'm using Sql on macOS. Thanks in advance! My development environment is Linux. I'm coming from a different tech stack and learning Sql. The stack includes Sql and several other technologies. Any help would be greatly appreciated! Any pointers in the right direction? The stack includes Sql and several other technologies. Any pointers in the right direction?