在JSON数组数据字段中查询数据 [英] Querying data within JSON array data field

问题描述

当前是第一次使用postgres 9.3中的JSON字段，而Im在查询数组时遇到了一些困难.

Currently playing with JSON fields in postgres 9.3 for the first time and Im running into some difficulty with querying arrays.

具有JSON数组数据类型的字段称为帐户"，一些示例数据如下所示:

The field with the JSON array data type is called 'accounts' and some sample data would be as follows

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

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

I want to be able to find the id of the company that owns account_id 123 for example. The query that I'm having trouble with currently is as follows:

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

这会导致错误:

WHERE的参数不能返回集合

argument of WHERE must not return a set

推荐答案

json_array_elements(...)返回一个集合，将->>和=应用于该集合的结果也将返回.观察:

json_array_elements(...) returns a set, and so does the result of applying ->> and = to the set. Observe:

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 (...)都不是，我认为我们将不得不对其进行修复.

You'd expect to just be able to write '123' = ANY (...) but that's not supported without an array input, unfortunately. Surprisingly, neither is '123' IN (...), something I think we're going to have to fix.

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

So, I'd use LATERAL. Here's one way, which will return a company ID multiple times if it has multiple matches:

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

这篇关于在JSON数组数据字段中查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！