How to get value from JSON with dynamic key in Postgres

user_4957620 0
Viewed 2282

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?

jsonb postgresql sql
1 Answers

You can use jsonb_each() to extract all elements as key/value pairs. Then you can filter by the keys and access the value objects:

demo:db<>fiddle

SELECT
    *,
    objects.value -> 'status'
FROM
    t,
    jsonb_each(mydata) objects
WHERE objects.key::text LIKE '%foo%'
user_39842210
Related