pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed clarification on how to extract or compare numeric values enclosed in jsonb
2+ messages / 2 participants
[nested] [flat]
* Need clarification on how to extract or compare numeric values enclosed in jsonb
@ 2018-12-19 19:52 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2018-12-19 19:52 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: dmytrocx75@gmail.com
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!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Need clarification on how to extract or compare numeric values enclosed in jsonb
@ 2019-01-24 03:03 Bruce Momjian <bruce@momjian.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2019-01-24 03:03 UTC (permalink / raw)
To: dmytrocx75@gmail.com; pgsql-docs@lists.postgresql.org
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 +
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-01-24 03:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-19 19:52 Need clarification on how to extract or compare numeric values enclosed in jsonb PG Doc comments form <noreply@postgresql.org>
2019-01-24 03:03 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox