Creating a joined array column from another array column - postgresql

Question

I have an array column called fields in table tt_test as follows:

fields
{A}
{B,C}
{D}
{E}

Table tt_config contains a field_id for each field:

field   field_id
A          1
B          2
C          3
D          4
E          5

From the two tables above, I need to create a third called tt_output

fields    field_ids
{A}          {1}
{B,C}       {2,3}
{D}          {4}
{E}          {5}

Fairly new to postgresql/sql so any help/pointers would be appreciated!

user330315 · Accepted Answer · 2021-06-27 09:34:31Z

2

One way is to use a scalar sub-query:

select t.fields, 
       array(select c.field_id
             from tt_config c
             where c.field = any(t.fields)) as field_ids
from tt_test t

answered Jun 27, 2021 at 9:34

user330315

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

2 Comments

divij_o Over a year ago

Thank you! I think the "any" function was the key here, I tried something similar with the "IN" operator but that selected field_ids as a list of all fields that every occur in tt_test:

divij_o Over a year ago

This was my incorrect approach: select A.fields, C.field_ids from TT_TEST A INNER JOIN ( select ARRAY_AGG(B.field_id) as field_ids from tt_config B where B.field IN (select UNNEST(fields) from TT_TEST) ) C on true

M.Kasaei · Accepted Answer · 2021-06-27 09:31:17Z

0

You can use union operator to join to tables. to use this operator it's necessary to have two identical headers.

You can use this query

select fields , null as field_ids from tt_test
union
select fields , field_ids from tt_config

answered Jun 27, 2021 at 9:31

M.Kasaei

3286 silver badges14 bronze badges

Collectives™ on Stack Overflow

Creating a joined array column from another array column - postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related