从 postgres 中的复杂嵌套结构中检索具有特定键名称的 json 元素

我在 postgres json 字段中有一个复杂的嵌套 json 结构。我想列出所有带有键“$type”的元素值，无论它们出现在嵌套结构中的哪个位置。该结构包含嵌套在多个深度的数组中的数组。我应该使用什么sql查询？

表结构为：

create table if not exists documents
(
  id text not null
    constraint documents_pkey primary key,
  value json not null
)

此递归函数从复杂的 jsonb 对象中提取所有属性：

create or replace function jsonb_extract_all(jsonb_data jsonb, curr_path text[] default '{}')
returns table(path text[], value text)
language plpgsql as $$
begin
    if jsonb_typeof(jsonb_data) = 'object' then
        return query 
            select (jsonb_extract_all(val, curr_path || key)).*
            from jsonb_each(jsonb_data) e(key, val);
    elseif jsonb_typeof(jsonb_data) = 'array' then
        return query 
            select (jsonb_extract_all(val, curr_path || ord::text)).*
            from jsonb_array_elements(jsonb_data) with ordinality e(val, ord);
    else
        return query
            select curr_path, jsonb_data::text;
    end if;
end $$;

用法示例：

with my_table(data) as (
select
    '{
        "$type": "a",
        "other": "x",
        "nested_object": {"$type": "b"},
        "array_1": [{"other": "y"}, {"$type": "c"}],
        "array_2": [{"$type": "d"}, {"other": "z"}]
    }'::jsonb
)

select f.*
from my_table
cross join jsonb_extract_all(data) f
where path[cardinality(path)] = '$type';

         path          | value 
-----------------------+-------
 {$type}               | "a"
 {array_1,2,$type}     | "c"
 {array_2,1,$type}     | "d"
 {nested_object,$type} | "b"
(4 rows)