How to do a join or SELECT FROM WHERE IN from an array?

Question

PostgreSQL

There are two tables. tokens and regions. The tones table has a region_id in the form of a json array

Returns the following result https://imgur.com/ZdtIlYz
https://imgur.com/5fY8Lvf

I need to extract all regions from the regions table. Tried like this:

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

But it doesn't work. Swears in the console on the json_array_elements operator.

How can I join on such a field or SELECT FROM as I want?

Answer 1

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

Answer 2

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

The json functions and operators in postgresql were explicitly implemented for objects, and sometimes there are not enough opportunities for working with json arrays of numbers.
Therefore, we bring it manually to the number.