How to compare numeric in PostgreSQL JSONB

Question

I ran into strange situation working with jsonb type.

Expected behavior

Using short jsonb structure:

{"price": 99.99}

I wrote query like this:

 SELECT * FROM table t WHERE t.data->price > 90.90

And it fail with error operator does not exist: jsonb > numeric the same as text (->>) operator does not exist: text > numeric

Then I wrote comparison as mentioned in many resources:

SELECT * FROM table t WHERE (t.data->>price)::NUMERIC > 90.90

And it's works as expected.

What's strange:

SELECT * FROM table t WHERE t.data->price > '90.90';

a little weird but query above works right.

EXPLAIN: Filter: ((data -> 'price'::text) > '90.90'::jsonb)

But if I change jsonb value to text as: {"price": "99.99"} there is no result any more - empty.

Question: How actually PostgreSQL compare numeric data and what preferable way to do this kind of comparison.

-> returns a JSON value, not a number

user330315
– user330315

2018-12-19 08:03:24 +00:00
Commented Dec 19, 2018 at 8:03 — user330315
– user330315, Commented Dec 19, 2018 at 8:03

Richard Huxton · Accepted Answer · 2018-12-19 08:00:36Z

19

But you aren't comparing numeric data, are you.

I can see that you think price contains a number, but it doesn't. It contains a JSON value. That might be a number, or it might be text, or an array, or an object, or an object containing arrays of objects containing...

You might say "but the key is called 'price' of course it is a number" but that's no use to PostgreSQL, particularly if I come along and sneakily insert an object containing arrays of objects containing...¹

So - if you want a number to compare to you need convert it to a number (t.data->>price)::NUMERIC or convert your target value to JSON and let PostgreSQL do a JSON-based comparison (which might do what you want, it might not - I don't know what the exact rules are for JSON).

¹ And that's exactly the sort of thing I would do, even though it is Christmas. I'm a bad person.

answered Dec 19, 2018 at 8:00

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1156168 Over a year ago

No doubt operator -> return jsonb value and ->> return text. The question was way jsonb and text are comparable and other types not, for example ( jsonb and numeric throw error). But any way, I got you idea, Thanks.

Richard Huxton Over a year ago

If you ever want to check the type of something there is a handy system function pg_typeof(<expression>)

mohammed mazin Over a year ago

Thanks for the answer I've been searching everywhere.

Collectives™ on Stack Overflow

How to compare numeric in PostgreSQL JSONB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related