1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ca5p3r
  • 83
  • 8
  • I would go with third table, because you have M:N (many to many) model. And then you just inner join the tables. – XraySensei Jan 09 '22 at 14:33
  • I am only able to query the data, not permitted to modify or create further tables. – ca5p3r Jan 09 '22 at 14:36
  • 2
    Ah I see.. In this case check this link https://stackoverflow.com/questions/54259414/replace-values-in-array-column-with-related-values-from-another-table – XraySensei Jan 09 '22 at 14:39
  • 1
    Thank you! That is exactly what I am looking for. Appreciate it. – ca5p3r Jan 09 '22 at 14:50

1 Answers1

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
mlewinska
  • 276
  • 1
  • 6