PostgreSQL: Unexpected behavior when using LATERAL JOIN with JSONB arrays in a subquery
I'm encountering an issue with a LATERAL JOIN while trying to extract values from a JSONB array in PostgreSQL 13. The goal is to flatten an array of objects stored in a JSONB column in order to join it with a related table. However, I'm getting unexpected results where some rows seem to produce duplicates when there shouldn't be any. Here's a simplified version of what I'm trying to do: ```sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id), items JSONB ); INSERT INTO users (name) VALUES ('Alice'), ('Bob'); INSERT INTO orders (user_id, items) VALUES (1, '[{"product": "Book", "quantity": 2}, {"product": "Pen", "quantity": 1}]'), (1, '[{"product": "Notebook", "quantity": 1}]'), (2, '[{"product": "Eraser", "quantity": 5}]'); SELECT u.name, item->>'product' AS product, (item->>'quantity')::int AS quantity FROM users u JOIN LATERAL jsonb_array_elements(orders.items) AS item ON orders.user_id = u.id; ``` The above query throws an error because `orders` is not defined in the FROM clause. I tried to correct it by including a proper JOIN to `orders`, but I still get a Cartesian product instead of the expected result. When I run this: ```sql SELECT u.name, item->>'product' AS product, (item->>'quantity')::int AS quantity FROM users u JOIN orders ON orders.user_id = u.id JOIN LATERAL jsonb_array_elements(orders.items) AS item ON true; ``` Now, the output is not what I expected. For Alice, I get multiple rows for the same order due to the multiple items in the JSONB array, which is correct, but I also seem to be seeing duplicate names in the results. I've attempted to use DISTINCT but it doesn't help because I need to get the full item details. I'm not sure how to handle this without losing the granularity of the items. Any suggestions on how to achieve the desired output without these duplicates? Are there any best practices for dealing with LATERAL joins and JSONB arrays in a scenario like this?