flatten_json.sql

create or replace function flatten(p_input jsonb, p_path text)
  returns table(path text, key text, value text)
as
$$
begin
  if jsonb_typeof(p_input) = 'array' then
    return query
      select f.*
      from jsonb_array_elements(p_input) as a(element)
        cross join flatten(a.element, p_path) f;
  else
    return query
      select p_path, e.key, e.value #>> '{}'
      from jsonb_each(p_input) as e(key, value)
      where jsonb_typeof(e.value) not in ('object', 'array')
        or (jsonb_typeof(e.value) = 'array' and jsonb_typeof(e.value -> 0) <> 'object')

      union all

      select f.*
      from jsonb_each(p_input) as t(key,value)
        cross join flatten(t.value, p_path||'/'||t.key) as f
      where jsonb_typeof(t.value) = 'object'

      union all

      select f.*
      from jsonb_each(p_input) as t(key,value)
        cross join jsonb_array_elements(t.value) as a(element)
        cross join flatten(a.element, p_path||'/'||t.key) as f
      where jsonb_typeof(t.value) = 'array'
        and jsonb_typeof(t.value -> 0) = 'object';
  end if;
end;
$$
language plpgsql
immutable
parallel safe
;

create or replace function flatten(p_input jsonb)
  returns table(path text, key text, value text)
as
$$
  select *
  from flatten(p_input, '');
$$
language sql
immutable
parallel safe
;