Now with a complete JSON operator set in pg9.4 and JSONB type, is expected to be use JSONB as a part of "PostgreSQL algebra"... Not JSONB as an "isolated universe"...
In performance terms, JSONB is faster than JSON, because is internally "more strongly typed" than JSON... As remembered here, "JSON operations take significantly more time than JSONB". So JSONB can be faster in type casting of its boolean, integer and number types... Let's go... We can't, why not?
MOTIVATION
Need a function as get_jsonb_value(jsonb,field,type)
(or a set of functions like jbval_to_int()
, jbval_to_float()
,jbval_to_boolean()
), to use the JSONB internal representation, and do a faster casting.
EDIT
If that kind of function (see formal requeriment examples below) not exist in the PostgreSQL Guide (there exist similar thing?) and it is not in a "next pg version" (it is a planed feature?)... Them, the question is "how to implement it in C, taking advantage of the internal JSONB representation?"
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;