current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 12 down vote favorite
3

Recently upgraded to using PostgreSQL 9.3.1 to leverage the JSONfunctionalities. In my table I have a json type column that has a structure like this:

{
   "id": "123",
   "name": "foo",
   "emails":[
      {
        "id": "123",
        "address": "somethinghere"
      },
      {
        "id": "456",
        "address": "soemthing"
      }
   ]
}

This is just dummy data for the purpose of the question.

Is it possible to query for a specific item in the emails array based on the id?
Pretty much: "return email where id=123)"?

share|improve this question

2 Answers 2

up vote 23 down vote accepted

Yes, that's possible:

SELECT *
FROM   tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE  elem->>'id' = 123;

tbl being your table name, json_col being the name of the JSON column.

More details in this related answer:

More about the implicit CROSS JOIN LATERAL in the last paragraph of this related answer:

Index to support this kind of query:

share|improve this answer
up vote 1 down vote

You can do it as simple as : 

SELECT * FROM table WHERE emails->>'id' = '123';

it seems you store the id as string, if it was an integer you can do it like this : 

SELECT *  from table WHERE cast(emails->>'id' as integer ) = 123  ;

or you can get all the rows with id > 10

SELECT *  from table WHERE cast(emails->>'id' as integer ) > 10  ;
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.