根据通配符键查询嵌套的JSON

Question

我有一个postgresql表，该表将JSON存储为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的结构：

{
    "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"
        }]
    }
}

每行将包含这样的JSON，其中时间戳是字典的键。

我想编写一个查询，该查询会从每行的JSON中获取所有foo。

我首先要获取所有键(在Python中这样做(：

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

然后在所有键上迭代，我运行查询(python pseudo代码(：

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

我将如何做到这是一个查询，而不是先获取所有键，然后迭代并获取foo项目。由于用作钥匙的时间戳可以是任何东西，因此能够执行诸如SELECT data->'%'->'foo' FROM history

Answer 1

使用 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)