Querying data within JSON array data field

Question

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

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"}]

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'

This results in an error:

argument of WHERE must not return a set

Answer 1

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)

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.

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