sql – How to get value from JSON with dynamic key in Postgres

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 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?