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

I'm guessing your intent is more like "if any of the elements have an account_id of 123 then return the corresponding company id" ... ?. — Craig Ringer
– Craig Ringer, Commented Feb 10, 2014 at 12:19

Craig Ringer · Accepted Answer · 2014-02-10 12:47:51Z

14

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

edited Feb 10, 2014 at 12:47

answered Feb 10, 2014 at 12:39

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying data within JSON array data field

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related