Ich habe eine komplexe verschachtelte JSON-Struktur in einem Postgres-JSON-Feld. Ich möchte alle Elementwerte mit dem Schlüssel '$ type' auflisten, unabhängig davon, wo sie in der verschachtelten Struktur erscheinen. Die Struktur enthält Arrays, die in Arrays mit einer Tiefe von mehreren Ebenen verschachtelt sind. Was ist die SQL-Abfrage, die ich verwenden sollte?
Die Tabellenstruktur lautet:
create table if not exists documents
(
id text not null
constraint documents_pkey primary key,
value json not null
)
Diese rekursive Funktion extrahiert alle Attribute aus einem komplexen jsonb-Objekt:
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 $$;
Anwendungsbeispiel:
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)
Dieser Artikel stammt aus dem Internet. Bitte geben Sie beim Nachdruck die Quelle an.
Bei Verstößen wenden Sie sich bitte [email protected] Löschen.
Lass mich ein paar Worte sagen