0

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;
Community
  • 1
  • 1
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
  • What is your question? –  Jan 03 '16 at 18:44
  • @a_horse_with_no_name Is about 1) the PostgreSQL guide, where this function? ... if this functio really not exists, is about 2) best way to implement that kind of function... Perhaps need C implementation. – Peter Krauss Jan 03 '16 at 18:49
  • I don't know ... which function ... you mean ... You do know... that all functions ... are documented ... in the ... manual? If ... a function ... isn't documented there ... then it doesn't exist ... –  Jan 03 '16 at 18:52
  • @a_horse_with_no_name The PostgreSQL Guide is so big and some confuse, I need to ask here to confirm... And I need to ask here to check if it will be a future issue of pg... But, sorry, I edit, refresh your question text. – Peter Krauss Jan 03 '16 at 18:54

0 Answers0