Postgresql SELECTing from JSON column

Question

Assume I am using PG 9.3 and I have a post table with a json column 'meta_data':

Example content of the json column 'meta_data'

{
  "content": "this is a post body",
  "comments": [
    {
      "user_id": 1,
      "content": "hello"
    },
    {
      "user_id": 2,
      "content": "foo"
    },
    {
      "user_id": 3,
      "content": "bar"
    }
  ]
}

How can I find all the posts where the user_id = 1 from the comments array from the meta_data column?

Looks to me like this would be rather better suited to traditionally relational modelling. Limited json support in Pg doesn't mean you should use json where regular relations will do just as well. — Craig Ringer
– Craig Ringer, Commented Sep 12, 2014 at 17:52
I should mention, the example is just a very over simplified version of the problem. In reality the JSON is much more complicated — Bill
– Bill, Commented Sep 12, 2014 at 18:34

mjallday · Accepted Answer · 2014-09-12 18:17:38Z

1

I'm almost positive I'm implementing this incorrectly but try this

select * 
from posts 
where id in (
    select id from (
        select id, 
            json_array_elements(meta_data->'comments')->'user_id' as user_id
        from posts
    ) x 
    where cast(user_id as varchar) = '1'
);

There's probably an array operator like @> that will remove the need for the nested select statements but I can't seem to get it to work right now.

Let me know if this is going down the correct track, I'm sure we could figure it out if required.

answered Sep 12, 2014 at 18:17

mjallday

10.2k9 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql SELECTing from JSON column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related