Thread: Need clarification on how to extract or compare numeric valuesenclosed in jsonb
Need clarification on how to extract or compare numeric valuesenclosed in jsonb
From
PG Doc comments form
Date:
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!
Re: Need clarification on how to extract or compare numeric valuesenclosed in jsonb
From
Bruce Momjian
Date:
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@momjian.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 +