Postgresql WHERE statement with JSON columns

Question

Assume the following posts table in a Postgresql 9.3 database

|id|name|properties|
--------------------

Where properties is a JSON column.

An example JSON would be:

{
  "comments":[
    {
      "user_id":1,
      "comment":"foo"
    },
    {
      "user_id":2,
      "comment":"bar"
    },
    {
      "oddCase":2,
      "thisShouldStillWork":true 
    }
  ]
}

How can I issue a SELECT statement with an "AND" or "or" WHERE?

So one would be able to:

Select all the posts where at least user_id 1 OR user_id 2 OR user_id n ... has commented.
Select all the posts where at least user_id 1 AND user_id 2 AND user_id n ... has commented on.

EDIT: Looks like json_array_elements with a dynamically generated query will work.

what postgres version? what does 'at least' mean? can at least be omitted? it seems that some of the contain operators would work: postgresql.org/docs/9.4/static/functions-json.html — Greg
– Greg, Commented Sep 11, 2014 at 15:54
This will work for the OR case? WITH tmp (posts) AS (SELECT json_array_elements(properties->'comments') det, id FROM posts) SELECT * FROM tmp JOIN posts p ON p.id=tmp.id WHERE posts->>'user_id'='1' OR posts->>'user_id'='2' — Jayadevan
– Jayadevan, Commented Sep 12, 2014 at 3:01

trvrm · Accepted Answer · 2014-09-12 19:30:47Z

1

Something like this should work for the second question.

 SELECT id,
 comment_user_id 
 FROM (
        SELECT id, 
               (json_array_elements(properties->'comments')->>'user_id')::int AS comment_user_i
          FROM post
    ) I 
 WHERE comment_user_id = any(ARRAY[1,2,3]);

answered Sep 12, 2014 at 19:30

trvrm

8146 silver badges19 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Postgresql WHERE statement with JSON columns

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related