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))