current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
    
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 Feb 10 '14 at 12:19

1 Answer 1

up vote 3 down vote accepted

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';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.