Обсуждение: Proposal for JSONB functions for internal representation casting insted text-casting
Proposal for JSONB functions for internal representation casting insted text-casting
От
Peter Krauss
Дата:
The usefulness of ->> operator is indisputable, but even with boolean or numeric values, with good binary internal representation, it returns JSONB value as text data type.
The simple (myJSONB->>'myField')::expectedType is not enough because:
1) there are no internal optimization, need two-step casting, first bynary-to-text, them text-to-expectedType.
2) if expectedType is not the expected (in the associated jsonb_typeof), generates an error... The ideal "return NULL" convention is not easy to implement with usual casting.
More details and some examples at
- - - -
CONTEXT OF USEFULNESS
As section "8.14. JSON Types" in the pg9.4 guide,
"Representing data as JSON can be considerably more flexible (...) is quite possible for both approaches to co-exist and complement each other (...) However, even for applications where maximal flexibility is desired, it is still recommended that JSON documents have a somewhat fixed structure".
The proposed casting functions of JSONB is a part of "predictable but fluid structure" demands in JSON representation, and makes it easier to write queries that mix usual data types with JSONB.
- - - -
CREATE FUNCTION jbval_to_numeric(JSONB, varchar) RETURNS numeric AS $f$ SELECT CASE WHEN jsonb_typeof($1->$2)='number' THEN ($1->>$2)::numeric ELSE NULL::numeric END;
$f$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION jbval_to_float(JSONB, varchar) RETURNS float AS $f$ SELECT CASE WHEN jsonb_typeof($1->$2)='number' THEN ($1->>$2)::float ELSE NULL::float END;
$f$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION jbval_to_int(JSONB, varchar, boolean DEFAULT true)
RETURNS int AS $f$ SELECT CASE WHEN jsonb_typeof($1->$2)='number' THEN CASE WHEN $3 THEN ($1->>$2)::int ELSE ($1->>$2)::float::int END ELSE NULL::int END;
$f$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION jbval_to_boolean(JSONB, varchar) RETURNS boolean AS $f$ SELECT CASE WHEN jsonb_typeof($1->$2)='boolean' THEN ($1->>$2)::boolean ELSE NULL::boolean END;
$f$ LANGUAGE sql IMMUTABLE;On Sun, Jan 3, 2016 at 3:24 PM, Peter Krauss <ppkrauss@gmail.com> wrote: > The usefulness of ->> operator is indisputable, but even with boolean or > numeric values, with good binary internal representation, it returns JSONB > value as text data type. > > The simple (myJSONB->>'myField')::expectedType is not enough because: > > 1) there are no internal optimization, need two-step casting, first > bynary-to-text, them text-to-expectedType. > > 2) if expectedType is not the expected (in the associated jsonb_typeof), > generates an error... The ideal "return NULL" convention is not easy to > implement with usual casting. Agreed, something like this would be useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Proposal for JSONB functions for internal representation casting insted text-casting
От
Pavel Stehule
Дата:
2016-01-06 19:52 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:
On Sun, Jan 3, 2016 at 3:24 PM, Peter Krauss <ppkrauss@gmail.com> wrote:
> The usefulness of ->> operator is indisputable, but even with boolean or
> numeric values, with good binary internal representation, it returns JSONB
> value as text data type.
>
> The simple (myJSONB->>'myField')::expectedType is not enough because:
>
> 1) there are no internal optimization, need two-step casting, first
> bynary-to-text, them text-to-expectedType.
>
> 2) if expectedType is not the expected (in the associated jsonb_typeof),
> generates an error... The ideal "return NULL" convention is not easy to
> implement with usual casting.
@2 looks little bit dangerous. If expected number isn't number, but a text, then I'll expect a exception in all cases.
Pavel
Agreed, something like this would be useful.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers