PostgreSQL - Replace IDs inside array with values from another table [duplicate]

Question

I have two tables with the following structure:

entities

id (int),
name (text)

activity_status

id (int)
is_active (boolean)
entities_ids (int[])

the activity status table contains only two records, with activity of True and False respectively, and the entities_ids is an array of entities that are either active or not, something like this:

SELECT * FROM activity_status

Query result:

1  True   [1, 5, 20, 66]
2  False  [15, 77, 30]

I need to create a SELECT statement to get the active entities' names from these tables instead of the IDs.

I would go with third table, because you have M:N (many to many) model. And then you just inner join the tables. — XraySensei
– XraySensei, Commented Jan 9, 2022 at 14:33
I am only able to query the data, not permitted to modify or create further tables. — ca5p3r
– ca5p3r, Commented Jan 9, 2022 at 14:36
Ah I see.. In this case check this link stackoverflow.com/questions/54259414/… — XraySensei
– XraySensei, Commented Jan 9, 2022 at 14:39
Thank you! That is exactly what I am looking for. Appreciate it. — ca5p3r
– ca5p3r, Commented Jan 9, 2022 at 14:50

mlewinska · Accepted Answer · 2022-01-09 15:29:03Z

3

Try this one:

with active_entities as (
select s.activity, s.entities_ids, ARRAY(select e.name from unnest(s.entities_ids)  WITH ORDINALITY AS a(entity_id, ord) 
             join entities e on e.id = a.entity_id
                  )  as full_name
 from activity_status s
where s.activity = TRUE)

select entities from active_entities ae, unnest(ae.full_name) as entities

answered Jan 9, 2022 at 15:29

mlewinska

2761 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - Replace IDs inside array with values from another table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related