Return element of jsonb field with activerecord select

Question

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'")

Answer 1

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'"))

Answer 2

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.