Postgres Pro
Downloads
Home   >   mailing lists

Proposal for JSONB functions for internal representation casting insted text-casting - Mailing list pgsql-hackers

From Peter Krauss
Subject Proposal for JSONB functions for internal representation casting insted text-casting
Date
Msg-id CAHEREts=3PVC4_wT55T4uJSowRmzPvCw-zLk0pOgEtzo3cyK8Q@mail.gmail.com
Whole thread Raw
Responses Re: Proposal for JSONB functions for internal representation casting insted text-casting  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
Tree view
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.

- - - -
Formal requeriment for a C implementation below

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;

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Some 9.5beta2 backend processes not terminating properly?
Next
From: Pavel Stehule
Date:
Subject: Re: count_nulls(VARIADIC "any")