recursive introspection: flatten (json) → (key, value, json_type)

flatten_json.sql

/*
  Flatten JSON tables
*/
create or replace function flatten_json(data jsonb)
    returns table (
        key       text,
        value     jsonb,
        json_type text)
as
$$
with recursive flatten (key, value, json_type) as (
    select key, value, jsonb_typeof(value)
    from jsonb_each(data)
    union all
    (with typed_values as (table flatten)
     select concat_ws('.', f.key, g.key), g.value, jsonb_typeof(g.value)
     from typed_values        f,
          jsonb_each(f.value) g
     where json_type = 'object'
     union all
     select concat_ws('.', f.key, index), array_value, jsonb_typeof(array_value)
     from typed_values        f,
          jsonb_array_elements(value)
              with ordinality g(array_value, index)
     where json_type = 'array'))
select *
from flatten
where json_type !~ 'object|array|null'
  and value #>> '{}' <> ''
$$
    language sql
    stable;
alter function flatten_json owner to postgres;