[SQL] Avoiding pointless ::int cast of a number field retrieved from a JSON

Please take a look at this query:

SELECT COUNT(*) FROM events e WHERE (e.event_data -> 'state')::int = -1;


The query works and everything is actually ok, but...

This is how the JSON object looks like when I read it from the database:

{
"eventUID": "3ea6baf7-8772-48a5-a32b-b00901534025",
"data": {<some-data>},
"state": 1
}


As you can see 'state' is an int type.

EXPLAIN ANALYZE show me this:

Aggregate (cost=15.08..15.09 rows=1 width=8) (actual time=0.042..0.076 rows=1 loops=1)
-> Seq Scan on events e (cost=0.00..15.07 rows=1 width=0) (actual time=0.018..0.027 rows=0 loops=1)
Filter: (((event_data -> 'state'::text))::integer = -1)
Planning Time: 0.139 ms
Execution Time: 0.198 ms


Obviously Postgres casts an already int field to a text and then casts it again to an int.

So is it possible to avoid the cast so the select looks something like this (I know this is not valid select):

SELECT COUNT(*) FROM events e WHERE e.event_data -> 'state' = -1;


Using PostgreSQL 16.

Continue reading...