Querying nested json based on wildcard key

Question

I have a PostgresQL table which stores a JSON as jsonb:

 Column |  Type   |                         Modifiers                         | Storage  | Stats target | Description 
--------+---------+-----------------------------------------------------------+----------+--------------+-------------
 id     | integer | not null default nextval('history_id_seq'::regclass)      | plain    |              | 
 data   | jsonb   | not null                                                  | extended |              | 

JSON's structure:

{
    "20180201": {
        "foo": [{
            "name": "item1",
            "desc": "an item 1"
        }, {
            "name": "item2",
            "desc": "an item 2"
        }],
        "bar": [{
            "name": "item1",
            "desc": "an item 1"
        }, {
            "name": "item2",
            "desc": "an item 2"
        }]
    }
}

Each row will contain such a JSON where timestamp is the key of the dictionary.

I want to write a query which'll fetch all foo from the JSON of each row.

I am doing this by first getting all keys (doing this in Python):

SELECT (jsonb_object_keys(data)) AS key FROM history;

Then iterating over all keys, I run the query (python pseudo-code):

for key in keys: 
    query = "SELECT data->'%s'->'foo'FROM history" % key
    fetch_and_print_all_rows()

How would I do this is a single query instead of first getting all keys and then iterating and getting the foo item. Since the timestamp which is used as key can be anything, would be able to do something like SELECT data->'%'->'foo' FROM history

Answer 1

Use jsonb_each():

select id, key, value->>'foo' as foo
from history
cross join jsonb_each(data)

 id |   key    |                                       foo                                        
----+----------+----------------------------------------------------------------------------------
  1 | 20180201 | [{"desc": "an item 1", "name": "item1"}, {"desc": "an item 2", "name": "item2"}]
(1 row)