A postgresql function for enumerating deep key/value pairs of a jsonb object

jsonb_deep_key_values.sql

create or replace function jsonb_deep_key_value (j jsonb)
  returns table (key text, value jsonb)
  as $$
  with recursive t(key, value) as (
      select
        jj.key,
        jj.value
      from
        jsonb_each(jsonb_build_object('', j)) as jj
    union all (
      select
        (
          case jsonb_typeof(t.value)
            when 'object' then (t.key || '.' || tt.key)
            else t.key
          end
        ) as key,
        (
          case jsonb_typeof(t.value)
            when 'object' then tt.value
            when 'array' then tt.value
            else t.value
          end
        ) as value
      from
        t inner join lateral (
            select ttt.key, ttt.value
            from jsonb_each(t.value) as ttt
            where jsonb_typeof(t.value) = 'object'
          union all
            select '', ttt.value
            from jsonb_array_elements(t.value) as ttt
            where jsonb_typeof(t.value) = 'array'
        ) as tt on true
    )
  )
  select
    substring(t.key, 2) as key,
    t.value
  from t
  where jsonb_typeof(t.value) not in ('object', 'array')
  $$ LANGUAGE SQL;

jsonb_deep_key_values.sql.README.md

jsonb_deep_key_values

Usage

On jsonb

select key, value
from jsonb_deep_key_value('{"A": { "B": "C" } }'::jsonb);

key	value
A.B	C

On Queryset

select key, value
from (
  select *
  from tbl
  where cond = true
) qs inner join lateral jsonb_deep_key_value(qs.jsonb_col) on true;

key	value
all	deep_values
...	...

For counting

select key, value, count(*) as count
from (
  select *
  from tbl
  where cond = true
) qs inner join lateral jsonb_deep_key_value(qs.jsonb_col) on true
group by key, value
order by count(*) desc;

key	value	count
k.k	v	3
...	...	...

Treat entire queryset as json (including non-json fields)

select key, value, count(*) count
from
  (
    select *
    from tbl
    where cond
  ) qs inner join lateral jsonb_deep_key_value(row_to_json(qs)::jsonb) on true
group by key, value
order by count desc
;