Proposal for JSONB functions for internal representation casting insted text-casting
| От | Peter Krauss |
|---|---|
| Тема | Proposal for JSONB functions for internal representation casting insted text-casting |
| Дата | |
| Msg-id | CAHEREts=3PVC4_wT55T4uJSowRmzPvCw-zLk0pOgEtzo3cyK8Q@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Proposal for JSONB functions for internal
representation casting insted text-casting
|
| Список | pgsql-hackers |
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;В списке pgsql-hackers по дате отправления: