Search code examples
sqlpostgresqldatabase-designsubqueryjsonb

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

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.

Solution

  • 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: