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?
0
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.key3returns the value under that key, found on any levelstrictmode prevents duplicatevalue3andvalue4that would be returned due to accessingkey2array twice in otherwise defaultlaxmodeWHERE metadata @? '$.**.key3'condition uses an index to only search those rows that havekey3anywhere in them
1