CODEWITHSUNDEEP
ruby-on-railsactiverecordjsonb
Carpela
Carpela

Reputation: 2195

Return element of jsonb field with activerecord select

How do I 'select' a value from within a jsonb stored field?

e.g. 

@model1:
data: {"special_date" => "Wed, 16 Mar 2016 11:20:20 -0700", ....}
@model2:
data: {"special_date" => "Wed, 23 Mar 2016 11:20:20 -0700", ....}

I want something like 

Model.all.select("data -> 'special_date'")

Upvotes: 6

Views: 5604

Answers (2)

coreyward
coreyward

Reputation: 80051

If you want a sparse ActiveRecord model instance to hold the data (rather than primitives in an array), you just use select:

model = Model.select("data -> 'special_date' as special_date'").first
model.id #=> nil
model.special_date = #=> "Wed, 23 Mar 2016 11:20:20 -0700"

If you want to fetch the entire record as well as a particular piece of data from within the jsonb column, you can chain on select:

model = Model.select('*').select("data -> 'special_date' as special_date").first
model.id #=> 42
model.special_date #=> "Wed, 23 Mar 2016 11:20:20 -0700"

If you don't need the ActiveRecord instance and are okay getting an array back, just use pluck:

Model.pluck("data -> 'special_date'")

Edit: as of Rails 6.1 plucking this way will trigger a ActiveRecord::UnknownAttributeReference error so you need to wrap the query with Arel.sql:

Model.pluck(Arel.sql("data -> 'special_date'"))

Upvotes: 14

Carpela
Carpela

Reputation: 2195

This isn't perfect since it uses pluck rather than select, but 

Model.pluck("data -> 'special_date'")

or if you need more than one column 

Model.pluck(:id, "data -> 'special_date'")

works.

Upvotes: 3

Related Questions