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
jsonb_path_query helps to retrive the values of keys. It worked.