Active Record query on first value in stored array on JSONB column

Question

I have a scenario where for an Item, I am logging dates it was 'active_at' into an array stored in a JSONB column.

So the data would look something like this:

active_at:
 {"dates"=>
   ["2018-07-22",
    "2018-09-05",
    "2018-09-06",
    "2018-09-07",
    "2018-09-14",
    "2018-09-15",
    "2018-09-16",
    "2018-10-20",
    "2018-10-21",
    "2018-10-30"]}

I can successfully query the column as follows for a date match within the array:

@item = Item.where('active_at @> ?', {dates: '2018-10-20'.split}.to_json)

However, I now need to be able to query for a match on the first entry in the array only. Entries in the array are always in date order as new values are appended to the end of the array.

I have tried things like this, but with no joy:

@item = Item.where('active_at @> ?', {dates['first']: '2018-10-20'.split}.to_json)
@item = Item.where('active_at @> ?', {dates.first: '2018-10-20'.split}.to_json)

Marcin Kołodziej · Accepted Answer · 2018-10-30 14:47:25Z

2

This should work:

Item.where("active_at->'dates'->>0 = ?", "2018-10-20")

SQL Fiddle

I'd be really concerned about the performance of this query and consider doing some schema changes.

answered Oct 30, 2018 at 14:47

Marcin Kołodziej

5,3231 gold badge12 silver badges18 bronze badges

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

1 Comment

Raoot Over a year ago

Great! That worked perfectly. I note your concerns about performance, however, this is being used for background reports and is a bit of an edge case.

Collectives™ on Stack Overflow

Active Record query on first value in stored array on JSONB column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related