Rails: Postgres JSONB "between" query [duplicate]

Question

I'm working with Rails 5 + Postgres.

I have a Postgres JSONB column named data with data that looks something like:

{username: 'McGruff', timestamp: 123456789}

I would like to query for data that is between two timestamps, to get a subset of records that all have a timestamp within some range (say, the last 24 hours).

Using comments from below, the answer is:

Model.where("(data->'timestamp')::int BETWEEN ? AND ?", start, end)

Thanks for the help!

Executive summary of the duplicate: -> gives you a jsonb value, not a string or integer; ->> gives you a string back, then cast that string to an integer to get the right comparison logic: (data->>'timestamp')::int between ? and ?. — mu is too short
– mu is too short, Commented Apr 24, 2019 at 17:33

uday · Accepted Answer · 2019-05-16 03:45:02Z

1

Use where clause by providing start & end inputs as strings. as the data stored in string format in jsonb column.

somewhat like below:

Model.where((data->>'timestamp')::int between ? and ?, start, end)

edited May 16, 2019 at 3:45

answered Apr 24, 2019 at 5:48

uday

1,48112 silver badges20 bronze badges

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

2 Comments

mu is too short Over a year ago

(1) -> yields a jsonb value, not a string. (2) Strings-containing-numbers don't compare the same as the numbers themselves.

curiouscode Over a year ago

Wow, thanks UdAY ... that's embarrassing. Using where combined with what @muistooshort said above was the solution: Model.where((data->>'timestamp')::int between ? and ?, start, end)

Collectives™ on Stack Overflow

Rails: Postgres JSONB "between" query [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related