arrays - 在 PostgreSQL 中查询 JSON 对象数组中的条件

Question

让我们假设我们有一个 PostgreSQL 数据库，其中的表具有以下类型的行：

id | doc
---+-----------------
1  | JSON Object
2  | JSON Object
3  | JSON Object
...

JSON 具有以下结构：

{
'header' : {
    'info' : 'foo'},
 'data' : 
[{'a' : 1, 'b' : 123},
 {'a' : 2, 'b' : 234},
 {'a' : 1, 'b' : 543},
 ...
 {'a' : 1, 'b' : 123},
 {'a' : 4, 'b' : 452}]
}

在表的所有行中的“数据”中的“a”和“b”具有任意值。

第一个问题：如何查询表中满足以下条件的行：

列表/数组中存在一个带有键“data”的字典，其中 a==i 和 b>j。

例如，对于 i=1 和 j=400，将满足上述示例的条件，并返回相应的列。

第二个问题：在我的问题中，我必须处理 Json 中的时间序列数据。每个测量值都由一个 Json 表示，因此表中的一行。我想确定发生某些事件的测量值。对于上述结构不适合方便查询的情况：这样的时间序列如何看起来更容易查询？

非常感谢！

标签： arraysjsonpostgresql

Answer 1

我相信这样的查询应该回答您的第一个问题：

select distinct id, doc
from (
    select id, doc, jsonb_array_elements(doc->'data') as elem
    from docs
  ) as docelem
where (elem->>'a')::int = 4 and (elem->>'b')::int > 400

db<>在这里摆弄