CODEWITHSUNDEEP
ruby-on-railspostgresql-9.4
Muhammed kanyi
Muhammed kanyi

Reputation: 89

how to query jsonb field with arrays inside

i have a jsonb field which have an array like this

[{"odd_id"=>"5328", "team_id"=>"", "event_id"=>"10301952", "odd_type"=>"full_time", "odd_label"=>"X", "odd_value"=>"3.29"}, {"odd_id"=>"5349", "team_id"=>"", "event_id"=>"10299004", "odd_type"=>"full_time", "odd_label"=>"X", "odd_value"=>"3.10"}, {"odd_id"=>"5237", "team_id"=>"2554", "event_id"=>"2020445", "odd_type"=>"full_time", "odd_label"=>"1", "odd_value"=>"2.87"}, {"odd_id"=>"5174", "team_id"=>"669", "event_id"=>"1685560", "odd_type"=>"full_time", "odd_label"=>"2", "odd_value"=>"3.39"}]

i want to use the where to query the event_id in the column. can someone direct me in right direction on how to archive this, by the way am using Rails 5 and PostgreSQL.

Upvotes: 2

Views: 771

Answers (1)

Anurag Aryan
Anurag Aryan

Reputation: 651

Let's assume you've model called Test with jsonb field test_field, where one particular value is array of hash, as shown:

[{"odd_id"=>"5328", "team_id"=>"", "event_id"=>"10301952", "odd_type"=>"full_time", "odd_label"=>"X", "odd_value"=>"3.29"}, {"odd_id"=>"5349", "team_id"=>"", "event_id"=>"10299004", "odd_type"=>"full_time", "odd_label"=>"X", "odd_value"=>"3.10"}, {"odd_id"=>"5237", "team_id"=>"2554", "event_id"=>"2020445", "odd_type"=>"full_time", "odd_label"=>"1", "odd_value"=>"2.87"}, {"odd_id"=>"5174", "team_id"=>"669", "event_id"=>"1685560", "odd_type"=>"full_time", "odd_label"=>"2", "odd_value"=>"3.39"}]

You've to query the database like:

Test.where('test_field @> ?', [{ 'event_id' => '10301952' }].to_json)

Upvotes: 3

Related Questions