I have a JSONB column called metadata
in my PostgreSQL database. I want to extract the values of a specific key within this column.
For example, consider the following JSON structure:
{
"key1": "value1",
"key2": [
{"key3": "value3"},
{"key3": "value4"}
]
}
I need to retrieve the values of key3
, even if they are nested at various levels (up to 4 or 5 levels deep). Is there a generic approach to dynamically generate a PostgreSQL SQL query for this purpose?
The jsonb_path_query()
function can do that: demo at db<>fiddle
create table your_table(
id int generated by default as identity primary key
,metadata jsonb);
insert into your_table(metadata) values
('{"key1": "value1",
"key2": [ {"key3": "value3"}
,{"key3": "value4"} ]
}')
,('{"key3": "value5",
"key2": [ {"key4": {"key3": "value6"}} ]
}');
SELECT jsonb_path_query(metadata,'strict $.**.key3')
FROM your_table
WHERE metadata @? '$.**.key3';
jsonb_path_query |
---|
"value3" |
"value4" |
"value5" |
"value6" |
$
is the root of the jsonb structure.**
means all levels of the structure are considered.key3
returns the value under that key, found on any levelstrict
mode prevents duplicate value3
and value4
that would be returned due to accessing key2
array twice in otherwise default lax
modeWHERE metadata @? '$.**.key3'
condition uses an index to only search those rows that have key3
anywhere in them