postgresql using json sub-element in where clause

Question

This might be a very basic question but I am not able to find anything on this online.

If I create a sample table :

 create table dummy ( id int not null, data json );

Then, if I query the table using the following query:

select * from dummy where data->'x' = 10;

Now since there are no records in the table yet and there is no such property as 'x' in any record, it should return zero results.

But I get the following error:

postgres=# select * from dummy where data->'x' = 10;
ERROR:  operator does not exist: json = integer
LINE 1: select * from dummy where data->'x' = 10;

However following query works:

select * from dummy where cast(data->>'x' as integer) = 10;

Am I missing something here or typecasting is the only way I can get an integer value from a json field ? If that's the case, does it not affect the performance when data becomes extremely large ?

Answer 1

Am I missing something here or typecasting is the only way I can get an integer value from a json field ?

You're correct, typecasting is the only way to read an integer value from a json field.

If that's the case, does it not affect the performance when data becomes extremely large ?

Postgres allows you to index functions including casts, so the index below will allow you to quickly retrieve all rows where data->>x has some integer value

CREATE INDEX dummy_x_idx ON dummy(cast("data"->>'x' AS int))

Answer 2

JSON operator ->> means Get JSON array element (or object field) as text, so type cast is necessary.

You could define your own JSON operator, but it would only simplify the code, without consequences for performance.