Search code examples
postgresqljsonb

How to access internal representation of JSONb?

In big-data queries the intermediary "CAST to text" is a performance bottleneck... The good binary information is there, at the JSONb datatype: how to rescue it?

Typical "select where" example:

 with t(x,j) as (select 'hello','{"flag1":true,"flag2":false}'::jsonb) 
 SELECT x FROM t 
 WHERE (j->>'flag1')::boolean AND NOT((j->>'flag2')::boolean)

The the "casting to text" is a big loss of performance. Ideal is a mechanism to do direct, from JSONb to Boolean, something as 

 WHERE (j->'flag1')::magic_boolean AND NOT((j->'flag2')::magic_boolean)

PS: it is possible using C++? Is possible a CREATE CAST C++ implementation to solve this problem?

Solution

  • The feature is implemented in Postgres 11:

    E.4.3.4. Data Types

    [...]

    Add casts from JSONB scalars to numeric and boolean data types (Anastasia Lubennikova)

    Db<>Fiddle.