Construct ARRAY of values from a subquery in Postgres and use it in a WHERE clause

Question

These are samples of the two tables I have:

Table 1

  material_id (int)        codes (jsonb)        
---------------------    -------------------------------      
        1                  ['A-12','B-19','A-14','X-22']           
        2                  ['X-106','A-12','X-22','B-19']        
        .
        .

Table 2

   user_id        material_list (jsonb)
 -----------    --------------------
     1                 [2,3]
     2                 [1,2]
     .
     .

Table 1 contains material IDs and an array of codes associated with that material.

Table 2 contains user IDs. Each user has a list of materials associated with it and this is saved an an array of material IDs

I want to fetch a list of user IDs for all materials having certain codes. This is the query I tried, but it threw a syntax error:

SELECT user_id from table2
WHERE material_list ?| array(SELECT material_id 
                             FROM table1 where codes ?| ['A-12','B-19]);

I am unable to figure out how to fix it.

Please make it a habit to disclose the verbatim error message. And always your version of Postgres. — Erwin Brandstetter, Sep 06 '21 at 02:44

Erwin Brandstetter · Accepted Answer · 2021-09-06T02:47:59.500

Your query fails for multiple reasons.

First, ['A-12','B-19] isn't a valid Postgres text array. Either use an array constant or an array constructor:

'{A-12,B-19}'
ARRAY['A-12','B-19']

See:

Next, the operator ?| demands text[] to the right, while you provide int[].

Finally, it wouldn't work anyway, as the operator ?| checks for JSON strings, not numbers. The manual:

Do any of the strings in the text array exist as top-level keys or array elements?

Convert the JSON array to a Postgres integer array, then use the array overlap operator &&

SELECT user_id
FROM   tbl2
WHERE  ARRAY(SELECT jsonb_array_elements_text(material_list)::int)
    && ARRAY(SELECT material_id FROM tbl1 where codes ?| array['A-12','B-19']);

I strongly suggest to alter your table to convert the JSON array in material_list to a Postgres integer array (int[]) for good. See:

Then the query gets simpler:

SELECT user_id
FROM   tbl2
WHERE  material_list && ARRAY(SELECT material_id FROM tbl1 where codes ?| '{A-12,B-19}');

db<>fiddle here

Or - dare I say it? - properly normalize your relational design. See:

How to implement a many-to-many relationship in PostgreSQL?

score 0 · Answer 2 · answered Sep 05 '21 at 20:05

This seems like the process of unnesting json arrays:

select t2.user_id
from table2 t2
where exists (select 1
              from table1 t1  join 
                   jsonb_array_elements_text(t2.material_list) j(material_id)
                   on t1.material_id = j.material_id::int join
                   jsonb_array_elements_text(t1.codes) j2(code)
                   on j2.code in ('A-12', 'B-19')
             );

Here is a db<>fiddle.

Construct ARRAY of values from a subquery in Postgres and use it in a WHERE clause

2 Answers2