json - 查找嵌套 JSON 文档中值大于 INT 的行

Question

我是一个 PostgreSQL 新手。但我认为在关系数据库中使用 json/jsonb 听起来很有趣。

//仅供引用:我尝试通过 C# .NET Core WebAPI 和 Dapper 实现它

我想要一个带有(多维)json 列的表，例如:

 id                                   | data                                                              _____________________________________|_________________________________________________________________________________                                      |                                                                                  4BF30FE6-D7DD-480B-8592-DC9676576FEF | { timestamps:{ "created":1578614541, "modified":1578615707 }, "type":"single"}   1AC2CD8F-09D0-456C-9FD4-B63E354BD324 | { timestamps:{ "created":1578614676, "modified":1578615921 }, "type":"multiple"} 50AD2D82-5919-4555-BCC2-B24E0DE24263 | { timestamps:{ "created":1578614552, "modified":1578615784 }, "type":"single"}   8C3BE671-17D1-49FD-A891-D5E69FDF7FC2 | { timestamps:{ "created":1578614685, "modified":1578615173 }, "type":"single"}   

And I want to get all ids which have data::timestamps.created greater than 1578614670.

Pseudo code:

SELECT id, data FROM table WHERE data::timestamps.created > 1578614670;

 id                                   | data                                                              _____________________________________|_________________________________________________________________________________                                      |                                                                                  1AC2CD8F-09D0-456C-9FD4-B63E354BD324 | { timestamps:{ "created":1578614676, "modified":1578615921 }, "type":"multiple"} 8C3BE671-17D1-49FD-A891-D5E69FDF7FC2 | { timestamps:{ "created":1578614685, "modified":1578615173 }, "type":"single"}   

有没有简单的方法可以做到这一点？

Answer 1

要下降多层嵌套 #>> operator很有用。然后将提取的 text 转换为适当的 numeric type比较之前。

SELECT id, data FROM tbl
WHERE (data #>> '{timestamps,created}')::numeric > 1578614670;

相当于:

...
WHERE (data -> 'timestamps' ->> 'created')::numeric > 1578614670;

numeric 是安全的选择。如果您知道 created 中的所有数字都小于 2^31 或 2^63，您可以使用 integer 或 bigint分别。您的示例显示了有效的数字文字，但这必须适用于所有 提取的值。

为了加快速度，考虑像 Miles 建议的表达式索引，转换为适当的数字类型 - 但要有适当的括号。

CREATE INDEX tbl_data_created_idx
ON tbl (((data #>> '{timestamps,created}')::numeric)); -- all parentheses required

相关:

• PostgreSQL Index on JSON

这里的所有内容都适用于类型 json 或 jsonb，在这方面都是一样的。

db<> fiddle here (同时展示)