is it possible to join two tables using an array of ids in the first table?

Example

orders table

create table orders
(
  id UUID DEFAULT uuid_generate_v4(),
  user_id INT REFERENCES users (id),
  products_ids JSON NOT NULL,
  checkout_date TIMESTAMP DEFAULT NOW()
);

products table

create table products
( 
  id SERIAL PRIMARY KEY, -- some other columns 
)

some records example

orders table

products table

I want to join the two tables by products_ids like

SELECT * 
FROM orders AS o 
  INNER JOIN products AS p ON p.id = ANY(o.products_ids) 
WHERE o.usre_id = 1 
ORDER BY o.checkout_date

CodePudding user response：

The correct solution is to properly normalize your data model and create a regular many-to-many relationship table.

However if you do want to go down the rabbit hole and use a de-normalized model, a native int[] array is slightly less bad than using a JSON column:

If product_ids is defined as int[] then indeed you can use the =any(...) operator:

select o.id, o.user_id, o.checkout_date, p.name
from orders o 
  join products p on p.id = any(o.product_ids);

Online example

If you do insist on using JSON, then you can use a JSON/Path query to define the join condition:

select o.id, o.user_id, o.checkout_date, p.name
from orders o 
  join products p on jsonb_path_exists(product_ids, '$[*] ? (@ == $id)', jsonb_build_object('id', p.id))

Alternatively

select o.id, o.user_id, o.checkout_date, p.name
from orders o 
  join products p on o.product_ids @@ ('$[*] == '||p.id::text)::jsonpath
;

This assumes that product_ids is defined as jsonb, not a json column.

Online example

But again: the correct solution is to use a properly normalized data model.

CodePudding user response：

In mysql, you use JSON_TABLE for this:

select orders.id, products.title from orders
join json_table(orders.product_ids,"$[*]" COLUMNS(product_id int path "$")) order_products
join products on products.id=order_products.product_id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2fe194aa6b192efe477efd365ec59bc0

https://stackoverflow.com/a/61732970/17389 may show how to do this in postgres, but I don't know enough about postgres's json support to say how performant it will be.