Return group of objects by referencing the item in the postgres array field

Asked

Viewed 31 times

0

I need a collection of objects having as reference the items of an array field in postgres, duplicating the person in the relationships she participates in.

I am using Rails 6 and created the field Relations thus:

t.integer :relations, array: true, null: false, default: []
...
add_index :people, :relations, using: :gin

Generating this table:

id | name |  relations  |
------------------------|
 1 | João | {nil,1,2,3} |
 2 | Maria| {nil,1}     |

I need to return according to the item in the Relations field array, the nil doesn’t matter.

Sort of like this

[
  {id: 1, name: 'João', relation: 1},
  {id: 1, name: 'João', relation: 2},
  {id: 1, name: 'João', relation: 3},
  {id: 2, name: 'Maria', relation: 1}
]

The coarse manner would be the return of that select:

SELECT id, name, '1' as relation FROM people WHERE 1 = ANY (relations)
union
SELECT id, name, '2' as relation FROM people WHERE 2 = ANY (relations)
union
SELECT id, name, '3' as relation FROM people WHERE 3 = ANY (relations);

I thank anyone who can help.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.