Query for element of array in JSON column

Question

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)"?

Erwin Brandstetter · Accepted Answer · 2022-03-27 02:11:49Z

59

For Postgres 9.4+ see adamc's later answer. Or:

Query for array elements inside JSON type

Original answer for Postgres 9.3

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 the name of the JSON column.

2 Comments

w0rp Over a year ago

This is the best answer I have seen for this type of question so far.

Cepr0 Over a year ago

@ErwinBrandstetter Sorry for off-top, but could you please help with this issue: stackoverflow.com/q/49532773 (Thanks in advance!)

Community · Accepted Answer · 2017-05-23 11:47:08Z

10

With a JSONB column in Postgres 9.4+ you can use the contains operator @> to query for an element in an array:

SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

See Query for array elements inside JSON type for more details.

Here is a working example:

CREATE TABLE jsontest(data JSONB NOT NULL);
INSERT INTO jsontest VALUES (
  '{
     "name": "foo",
     "id": "123",
     "emails": 
     [
       {
         "address": "somethinghere",
         "id": "123"
       },
       {
         "address": "soemthing",
         "id": "456"
       }
     ]
  }'
);
SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

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

(1 row)

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Jan 30, 2017 at 1:54

adamc

1,40520 silver badges17 bronze badges

2 Comments

Pranay Soni Over a year ago

how to delete address by id 123 ?

adamc Over a year ago

delete FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

Deepak Mahakale · Accepted Answer · 2016-08-08 11:45:01Z

3

Came across this post and found that you can directly query on table like this:

SELECT *
FROM   table_name, json_array_elements(json_column) AS data
WHERE  data->>'id' = 123;

Omitting this part:

json_array_elements(t.json_col->'emails')

answered Aug 8, 2016 at 11:45

Deepak Mahakale

23.8k10 gold badges73 silver badges94 bronze badges

1 Comment

Mercutionario Over a year ago

this is super super useful I have been struggling with CTE to try and make it work, and this just solves my need beautifully. Thanks!

Yunis Hawwash · Accepted Answer · 2015-05-12 13:01:11Z

-1

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  ;

answered May 12, 2015 at 13:01

Yunis Hawwash

983 silver badges13 bronze badges

1 Comment

adamc Over a year ago

I've tried this on Postgres 9.4.4 and it doesn't work:

Collectives™ on Stack Overflow

Query for element of array in JSON column

4 Answers 4

Original answer for Postgres 9.3

2 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Original answer for Postgres 9.3

2 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related