How to query an array of JSON in PostgreSQL 9.3?

Question

I created a table named 'delivery' and one of its field is an array of JSON.

CREATE TABLE delivery
(
  id text NOT NULL,
  date timestamp with time zone NOT NULL,
  items json NOT NULL,
  CONSTRAINT delivery_pkey PRIMARY KEY (id)
)

And here are the example data:

"1", "2012-01-08 20:54:38.541989+00", "[ {"id":1, "qty": 10 }, {"id":2, "qty": 300}]"
"2", "2012-01-08 22:44:10.016285+00", "[ {"id":1, "qty": 200}]"

How can I get the rows where the 'id' within the 'items' field is equal to 1?

1) using a text column for a primary key (where an integer could be possible) can be considered bad design. 2) putting repeating groups inside a blob-like object (such as json) violates 1NF and can be considered bad design, too. — wildplasser
– wildplasser, Commented Jul 14, 2014 at 10:15

Konstantin V. Salikhov · Accepted Answer · 2014-07-14 09:30:16Z

1

You can do it using subquery and json fucntions like this:

SELECT * FROM delivery
WHERE '1' in (
   select a->>'id' from json_array_elements(items) a
)

Here is SQL fiddle to play with.

answered Jul 14, 2014 at 9:30

Konstantin V. Salikhov

4,6512 gold badges38 silver badges48 bronze badges

Add a comment |

Ilesh Patel · Accepted Answer · 2014-07-14 09:21:57Z

-1

Try this:

SELECT * FROM delivery WHERE items->>'id' = 1

answered Jul 14, 2014 at 9:21

Ilesh Patel

2,15516 silver badges27 bronze badges

1

I tried this one but it gives error. It looks like you can't directly parse/access an array of JSON.

eSPiYa
– eSPiYa

07/14/2014 09:40:38
Commented Jul 14, 2014 at 9:40

Add a comment |

Collectives™ on Stack Overflow

How to query an array of JSON in PostgreSQL 9.3?

2 Answers 2

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related