The following query gets all key names that are needed from a jsonb
object. Basically, getting all key names that contain 'foo' from a column called 'objects'.
SELECT keys
FROM (
SELECT jsonb_object_keys(objects) as keys
from table
) as testvalues
WHERE keys LIKE '%foo%';
Once I get the set of key names, I want to access the status
field of that object as follows
SELECT objects->'hello_foo'->'status' FROM table
However, I still can't find a way to go through the set of key names in the first query and dynamically access the status
field.
What would be the best implementation?
You can use jsonb_each()
to extract all elements as key/value pairs. Then you can filter by the key
s and access the value
objects:
demo:db<>fiddle
SELECT
*,
objects.value -> 'status'
FROM
t,
jsonb_each(mydata) objects
WHERE objects.key::text LIKE '%foo%'
You can use funciton jsonb_extract_path
.
For example, if you have table my_json_data
, whihc has fields row_id
and json_data
, then you can use query below:
SELECT jsonb_extract_path(json_data, 'pathName') AS my_value
FROM my_json_data WHERE row_id = 100;
You can replace 'pathName'
with variable etc.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With