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
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

trvrm trvrm · Accepted Answer · 2014-09-12T19:30:47

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]);

Postgresql WHERE statement with JSON columns

1 Answers