Как сделать join или SELECT FROM WHERE IN из массива?

Question

[beduin01]

Есть две таблицы. tokens и regions. В таблице tonens есть region_id в виде json массива

Возвращает вот такой результат https://imgur.com/ZdtIlYz
https://imgur.com/5fY8Lvf

Мне нужно из таблицы regions извлечь все регионы. Пробовал так:

SELECT
	"ru_name"
FROM "public"."regions"
WHERE 
	"id" in ( SELECT json_array_elements(regions) FROM "tokens" where user_id = 5  );

Но не получается. Ругается в консоли на оператор json_array_elements.

Как мне сделать join по такому полю или SELECT FROM как я хочу?

Answer 1

[Slava Rozhnev]

SELECT 
	ru_name 
FROM "tokens" 
JOIN regions ON regions.id = any (tokens.regions)
WHERE user_id = 5 ;

PostgreSQL fiddle here

with reg_ids as (
  select unnest(regions) reg_id
  from tokens where user_id = 5
) select regions.*
from reg_ids
join regions on regions.id = reg_id;

One more fiddle

Comments

[beduin01]

спасибо, а почему ANY а не IN ?

[beduin01]

Кстати у меня на компе ругается на первый запрос: ОШИБКА: для операторов ANY/ALL (с массивом) требуется массив справа. Что-то сходу не пойму в чем может быть проблема.

У regions у меня тип JSON. Он сильно дело меняет? Стоит ли его использовать?

Answer 2

[Melkij]

select ... from regions 
where id in (
    select j::int 
    from tokens cross join json_array_elements_text(regions) as j 
    where user_id = 5
);

Функции и операторы json в postgresql явно реализовывались под объекты и возможностей по работе с json массивами чисел порой не хватает.
А потому приводим вручную к числу.

Comments

[beduin01]

А что тут cross join делает?

[Melkij]

ммм, в каком смысле? кроссджойнит вызов set-returning функции.