PostreSQL JSONB helpers functions

jsonb_helper.sql

-- jsonb_select_keys take jsonb and keys and return jsonb
-- contains only given keys
create or REPLACE function jsonb_select_keys (resource jsonb, keys text[] ) returns jsonb
as $$
select jsonb_object_agg(k, v)
from
  (select unnest(keys) k ) k ,
  lateral (select resource->k.k as v) t;
$$ LANGUAGE SQL
IMMUTABLE;

-- jsonb_array_select_keys the same as jsonb_select_keys
-- but iterate over array into jsonb
create or REPLACE function jsonb_array_select_keys (resource jsonb, keys text[] ) returns jsonb
as $$
select jsonb_agg(s)
from
  (select jsonb_array_elements(resource) v) val,
  lateral (select jsonb_select_keys(val.v, keys) as s) _v ;

$$ LANGUAGE SQL
IMMUTABLE;

-- jsonb_extract_keys take jsonb and array of keys
-- return jsonb with vals from resource in order of keys
create or REPLACE function jsonb_extract_keys (resource jsonb, keys text[] ) returns jsonb
as $$
select jsonb_agg(v)
from
  (select unnest(keys) k ) k ,
  lateral (select resource->k.k as v) t;
$$ LANGUAGE SQL
IMMUTABLE;

-- array_string_agg take array of strings and separator
-- return new concatinated by separator string
create or REPLACE function array_string_agg (resource text[], separator text ) returns text
as $$
select string_agg(k::text, separator)
from
  (select unnest(resource) k ) k ;
$$ LANGUAGE SQL
IMMUTABLE;

-- jsonb_extract_keys_text same as jsonb_extract_keys
-- but return array of strings
create or REPLACE function jsonb_extract_keys_text (resource jsonb, keys text[] ) returns text[]
as $$
select array_agg(v)
from
  (select unnest(keys) k ) k ,
  lateral (select resource->>k.k as v) t;
$$ LANGUAGE SQL
IMMUTABLE;


-- jsonb_concat_keys concatinate selected keys from resouce
-- delimited by separator
create or REPLACE function jsonb_concat_keys (resource jsonb, keys text[], separator text ) returns text
as $$
select array_string_agg(jsonb_extract_keys_text(resource, keys), separator) ;
$$ LANGUAGE SQL
IMMUTABLE;

-- jsonb_array_concat_keys the same as jsonb_concat_keys
-- but take resouce with array of objects and return text[]
create or REPLACE function jsonb_array_concat_keys (resource jsonb, keys text[], separator text ) returns text[]
as $$
select array_agg(s)
from
  (select jsonb_array_elements(resource) v) val,
  lateral (select array_string_agg(jsonb_extract_keys_text(val.v, keys), separator) as s) _v ;
$$ LANGUAGE SQL
IMMUTABLE;