Re: Need clarification on how to extract or compare numeric values enclosed in jsonb

On Wed, Dec 19, 2018 at 07:52:57PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/11/functions-json.html
> Description:
>
> A really strange behavior has been noticed when trying to extract numeric
> key value from jsonb and compare it to a number
> https://stackoverflow.com/questions/53841916/how-to-compare-numeric-in-postgresql-jsonb
> The question is: what is the recommended (or fastest) way to do this kind of
> comparisons?
> The question has arisen because the way postgres processes the mentioned
> comparisons is not clear, even though I've read the documentation
> carefully.
> Thank you!

The Stackoverflow thread seems pretty clear. While JSON values in
Javascript know what types they are, when values are coming out of JSON
into SQL, you have to cast them to a type that is supported by the
operators Postgres supports. Remember a column name can represent a
value for each row, and those values can have different internal JSON
types, e.g. string, number, boolean.

I guess if we were a JSON-only database we might have many operators
that understand JSON and can cast each value to the proper type for
comparison (we have a few that do), but we aren't only-JSON, so we
require the JSON value be cast to a fixed type for _all_ column values.

In psql, if you do '\do <' you can see the data types supported for '<',
and if you do '\dC numeric' you can see the casts from different types
to numeric. For Postgres to process a query, the column, with an
explicit or implicit cast, must match an supported operator for the '<'
symbol.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +