Select where json contains-CodePudding

I have a postgres table:

      Column      |          Type           
------------------ ------------------------ 
 id               | bigint                 |
 foo_id           | integer                |
 foo_name         | character varying(255) |
 bar_id           | json                   |

With values

     id |      foo_id    |       foo_name                |             bar_id
-------- ---------------- ------------------------------- ----------------------------
     17 |         717639 | name 1                        | [910, 5383774, 437, 5350]
     18 |        8442028 | name 2                        | [7274, 6241861, 437, 73669939]
     19 |          77415 | name n                        | [7274, 5513, 249642574, 743181502]

How can I select from this table records ids where bar_id contains value 437?

CodePudding user response：

You can use the operator @> to check if the array contains a specific value.

However that only works with jsonb values, not json values. So you need to cast your column.

select *
from the_table
where bar_id:jsonb @> '[437]';

It would be better to change the column's type to jsonb.