问题:Postgres JSONB - 查询所有根键

PostgreSQL (13.2)

我有一个包含 2 列的表 - 时间戳和快照。快照包含一个 JSON - 表示仓库状况的字典。数据结构是这样的

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

所以我需要在 json 中获取包含我正在寻找的数据的特定键。我还需要获取保存快照的行时间戳。我该如何查询这个?

附言。在 python 中,我可以使用 dict.keys 之类的东西,然后开始循环遍历所有键,我在这里需要类似的东西来搜索数据。

pps。知道如何用 SQLAlchemy 实现它也会很有趣

[来自 DB 查看器的屏幕截图]

解答

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