sql - 查询 JSON 数组数据字段中的数据

Question

目前第一次在 postgres 9.3 中使用 JSON 字段，我在查询数组时遇到了一些困难。

JSON 数组数据类型的字段称为“accounts”，一些示例数据如下

[{name: "foo", account_id: "123"}, {name: "bar", account_id: "321"}]

例如，我希望能够找到拥有 account_id 123 的公司的 ID。我目前遇到问题的查询如下:

select id from companies where json_array_elements(accounts)->>'account_id' = '123'

这会导致错误:

argument of WHERE must not return a set

Answer 1

json_array_elements(...) 返回一个集合，对集合应用 ->>> 和 = 的结果也是如此。观察:

regress=> select json_array_elements('[{"name": "foo", "account_id": "123"}, {"name": "bar", "account_id": "321"}]') ->> 'account_id' = '123';
 ?column? 
----------
 t
 f
(2 rows)

您希望能够编写 '123' = ANY (...) 但不幸的是，如果没有数组输入则不支持。令人惊讶的是，'123' IN (...) 也不是，我认为我们必须解决这个问题。

所以，我会使用 LATERAL。这是一种方法，如果它有多个匹配项，它将多次返回公司 ID:

CREATE TABLE company AS SELECT 1 AS id, '[{"name": "foo", "account_id": "123"}, {"name": "bar", "account_id": "321"}]'::json AS accounts;

SELECT id 
FROM company c,
LATERAL json_array_elements(c.accounts) acc 
WHERE acc ->> 'account_id' = '123';