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

Essential missing detail: does every row have a `foo` item? If not, what's the expected percentage? It would be needlessly expensive to process *all* rows, if you are only interested in a small percentage. — Erwin Brandstetter, Feb 06 '18 at 23:26
Yes, every row will have a `foo` item. If there is nothing for `foo`, it might be an empty array. But the chances of it are low. I.e the item itself won''t be missing. — Bharat, Feb 06 '18 at 23:30
Then klin's answer is exactly what you need. (Maybe `->` instead of `->>`.) — Erwin Brandstetter, Feb 06 '18 at 23:36

score 7 · Accepted Answer · answered Feb 06 '18 at 20:51

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)

Querying nested json based on wildcard key

1 Answers1

Linked