CODEWITHSUNDEEP
postgresqljsonb
Răzvan Flavius Panda
Răzvan Flavius Panda

Reputation: 22106

ERROR 42883: operator does not exist: text / text

When running the following query:

select data from example
where (data -> 'properties' ->> 'outageCount') / (data -> 'properties' ->> 'trackedCount') > 0.01

I'm getting the following error:

ERROR 42883: operator does not exist: text / text
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Both outageCount and trackedCount are stored as integers in the JSONB.

I tried casting using as float but it gave following error:

[42601] ERROR: syntax error at or near "as"

Upvotes: 2

Views: 7686

Answers (1)

fphilipe
fphilipe

Reputation: 10056

Even if the field is a JSON number, it will be returned as text (see json and jsonb operators):

db=# select pg_typeof(('{"foo":3}'::jsonb)->>'foo');
 pg_typeof
-----------
 text
(1 row)

You need to cast it by appending ::float to the expression:

db=# select pg_typeof((('{"foo":3}'::jsonb)->>'foo')::float);
    pg_typeof
------------------
 double precision
(1 row)

In your case:

select data from example
where (data -> 'properties' ->> 'outageCount')::float / (data -> 'properties' ->> 'trackedCount')::float > 0.01

Upvotes: 2

Related Questions