SQL: How to extract only rows matching condition 'WHERE column1 ILIKE column2' (where colu-CodePudding

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.

CodePudding user response：

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

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

CodePudding user response：

Try this:

select * from my_table where id = any(stores);

CodePudding user response：

Sample :

create table stores_table (id serial, stores jsonb);

Then add samples values :

insert into stores_table (stores) values ('[2,5]'), ('[2, 5]'), ('[2,6]'), ('[4,7]');

Search stores containing id :

select * from stores_table where stores @> to_jsonb(id);

You will get :

 id | stores
 --- --------
  2 | [2, 5]
  4 | [4, 7]
 (2 rows)

Hope this help.