postgresql using json sub-element in where clause
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))
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.