Your Location is: Home > Postgresql-9.5

Can not filter by '0000000000000' jsonb column

From: Spain View: 2018 a_subscriber 

Question

data is jsonb type

this query work

select id, modified_at, data,states from order where  data->'id' = '2100000044078'

nice

but this not work

select id, modified_at, data,states from shop_order where  data->'id' = '0000000000000'

error:

LINE 2: ..., data,states from order where  data->'id' = '000000000...
                                                             ^
DETAIL:  Token "0000000000000" is invalid.
CONTEXT:  JSON data, line 1: 0000000000000
SQL state: 22P02
Character: 161

Best answer

-> returns a jsonb value, but you are trying to compare that with a text column. So Postgres tries to convert the value on the right hand side to jsonb.

Use the ->> operator instead which returns the value as text

where data->>'id' = '0000000000000'