Postgres JSONB - 查询所有根键

Question

Postgres JSONB - 查询所有根键

3

PostgreSQL（13.2）

我有一个带有2个列的表 - 时间戳和快照。快照包含JSONB格式的字典，表示仓库的情况。数据结构如下：

{
    "key1": {...},
    "key2": {...},
    "key3": {...},
    "key4": {...},
    "key5": {...}
}

我需要获取JSON中包含所需数据的特定键，并获取保存快照的行时间戳。我该如何查询？

附注：在Python中，我可以使用类似dict.keys的方式开始循环遍历所有键，这里需要类似的方法来搜索数据。

另外，使用SQLAlchemy实现也很有趣。

[来自数据库查看器的截图]

- Wotori Movako

这需要澄清：行时间戳存储在哪里？在这个上下文中，什么是快照？ - Laurenz Albe

Laurenz，它存储在数据库表中，快照是一个JSONB字典，保存了仓库的当前状态。请查看下面问题附带的截图以更好地理解上下文。谢谢！ - Wotori Movako

数据必须像这样结构化吗？将仓库的数组作为warehouse_name：r9等，然后将其他数据放在同一个对象中，这样不是更好吗？ - Johnny John Boy

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ReBoOT · Accepted Answer

（jsonb_each()）。*从jsonb字段中选择所有键和值：

SELECT (JSONB_EACH(blocks)).*
FROM snapshots

然后我们需要通过嵌套键获取值：

SELECT key AS rack_num
     , value ->> 'client_id' AS client_id
     , value ->> 'timestamp' AS _TIMESTAMP
FROM (
      SELECT (JSONB_EACH(blocks)).*
      FROM snapshots
     ) AS t

最后，聚合结果：

SELECT rack_num
       , MIN(_TIMESTAMP) AS appearance_time
FROM (
      SELECT KEY                   AS rack_num
           , VALUE ->> 'client_id' AS client_id
           , VALUE ->> 'timestamp' AS _TIMESTAMP
      FROM (
            SELECT (JSONB_EACH(blocks)).*
            FROM snapshots
           ) AS t
      ) AS racks
WHERE client_id = :client_id
GROUP BY rack_num, client_id
ORDER BY MIN(_TIMESTAMP) DESC;