postgresql - 在嵌套的 JSONB 文档中按条件查询数据集

Question

我在 PostgreSQL 数据库 (v 9.6) 中有一个相当简单的表:

CREATE TABLE foobar (id serial, data jsonb);

这是存储在数据列中的 JSONB 文档的示例:

[{ key: 'foo', value: 100 }, { key: 'bar', value: 5 }, { key: 'baz', value: 10 }]

我正在尝试编写一个选择，它将返回满足 JSONB 文档中条件的每一行，并仅选择指定的嵌套文档；即对于 foo 大于 X 的每一行，返回所有以 baz 为键的嵌套文档。

到目前为止，我遇到过这样的查询，但它不起作用 — 它返回 0 条记录。

SELECT id
  FROM foobar
 WHERE (data->>'key' = 'foo' AND (data->>'value')::numeric > 5)
    OR (data->>'key' = 'bar' AND (data->>'value')::numeric < 10)

如果有人知道如何优化最终查询，那就太好了。谢谢!

Answer 1

for every row where foo is greater than X return all nested documents that have baz as key.

使用 jsonb_array_elements(data) 两次。第一个用于将值与键 foo 进行比较，第二个用于查找具有键 baz 的对象:

with foobar(id, data) as (
values
(1, 
'[
    { "key": "foo", "value": 100 }, 
    { "key": "bar", "value": 5 }, 
    { "key": "baz", "value": 10 }
]'::jsonb)
)

select id, value_baz
from foobar,
jsonb_array_elements(data) el_foo(value_foo),
jsonb_array_elements(data) el_baz(value_baz)
where value_foo->>'key' = 'foo' and (value_foo->>'value')::numeric > 5
and value_baz->>'key' = 'baz';

 id |          value_baz          
----+-----------------------------
  1 | {"key": "baz", "value": 10}
(1 row)

---

您的 json 列的格式很奇怪。我认为没有理由在这里使用 json 数组。您可以像这样以简单的形式存储相同的信息:

'{ "foo": 100, "bar": 5, "baz": 10 }'

在这种情况下，您的查询可能很简单:

with foobar(id, data) as (
values
(1, '{ "foo": 100, "bar": 5, "baz": 10 }'::jsonb)
)

select id, data->'baz' as baz
from foobar
where (data->>'foo')::numeric > 5;

 id | baz 
----+-----
  1 | 10
(1 row)