Question 113912 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

3

Answers


  1. 0

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

    select *
from the_table
where stores @> to_jsonb(id)
    Login or Signup to reply.
  2. 0

    Try this:

    select * from my_table where id = any(stores);
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search