Querying Postgres JSONB column by key with dynamic values

Question

I have model UserPost which has JSONB column posts and the data stored in that will be JSON having user_id as the key (dynamic) and some attributes as values. Consider that I have multiple user_posts records like below,

Record 1:

 id: 1
 posts: { 
          1: { message: 'some', posted_at: 'sometime', attachment_url: 'some_url' },
          3: { message: 'some', posted_at: 'sometime', attachment_url: 'some_url' }
        }

Record 2:

 id: 2
 posts: { 
   { 
      2: { message: 'some', posted_at: 'sometime', attachment_url: 'some_url' },
      5: { message: 'some', posted_at: 'sometime', attachment_url: 'some_url' }
    }

Record 3:

id: 3
 posts: { 
   { 
      1: { message: 'some', posted_at: 'sometime', attachment_url: 'some_url' },
      2: { message: 'some', posted_at: 'sometime', attachment_url: 'some_url' }
    }

I have user_ids say 2, 5 for which UserPost records have to be fetched, how will I do it?

Suganya · Accepted Answer · 2017-05-22 07:38:20Z

1

Found it,

Single key:

UserPost.where("posts ? '2'")

Array of keys:

UserPost.where('posts ?| array[:keys]', keys: ['1', '2'])

answered May 22, 2017 at 7:38

Suganya

6527 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Querying Postgres JSONB column by key with dynamic values

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