How to extract only rows matching condition ‘WHERE column1 ILIKE column2’ (where column1 is integer and column2 is jsonb)?

I am using postgresql.

Let’s suppose I have this table name my_table:

  id | idcm |  stores |     du     |     au     |              dtc              | 
  ----------------------------------------------------------------------------------
   1 | 20447 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   2 | 20456 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   3 | 20478 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   4 | 20482 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   5 | 20485 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 
   6 | 20497 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |
   7 | 20499 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |

I want to select only the rows having the value of id equal to one of the elements of the array in stores (of that line).
However, the type of stores is not array, it is jsonb.

So I want to get something like this:

  id | idcm |  stores |     du     |     au     |              dtc              | 
  ----------------------------------------------------------------------------------
   2 | 20456 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   5 | 20485 | [7, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 
   6 | 20497 | [2, 6] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |
   7 | 20499 | [5, 7] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |

I have tryed with

select * from my_table where stores::text ilike id::text;

but it returns zero rows because I would need to put wildcard character % before and after id,
so I have tryed with

select * from my_table where stores::text ilike %id%::text;

but I get a syntax error.

Answer

You can use the contains operator after converting the ID to a single JSON value:

select *
from the_table
where stores @> to_jsonb(id)

Advertisement

Answer