CODEWITHSUNDEEP
ruby-on-railspostgresqljsonb
Suganya
Suganya

Reputation: 652

Querying Postgres JSONB column by key with dynamic values

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?

Upvotes: 0

Views: 545

Answers (1)

Suganya
Suganya

Reputation: 652

Found it,

Single key:

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

Array of keys:

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

Upvotes: 1

Related Questions