基于通配符查询嵌套的json [英] Querying nested json based on wildcard key

问题描述

我有一个PostgresQL表，该表将JSON存储为jsonb:

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的结构:

{
    "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，其中timestamp是字典的键.

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

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

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

我首先要获取所有密钥(在Python中执行此操作):

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

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

然后遍历所有键，我运行查询(python伪代码):

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()

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

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

推荐答案

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

这篇关于基于通配符查询嵌套的json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！