CODEWITHSUNDEEP
databasepostgresql
user1156168
user1156168

Reputation: 956

How to compare numeric in PostgreSQL JSONB

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.

Upvotes: 11

Views: 10713

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

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...1

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

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

Upvotes: 17

Related Questions