lundi 18 avril 2022

Query on JSONB column to extract records between 2 dates

I am struggling to write a query in a Rails model which will retrieve all records with the date between two dates( dynamic dates from UI input).

The column is a jsonB column looking like this:

"170"=>[{"name"=>"Conflict Date/Time", "value"=>"2022-04-14 14:51:29", "valueUTC"=>"2022-04-14 13:51:29"}],

This record should be a valid one returned for a search with start_date: "2022-04-12 13:22:55" end_date: "2022-04-18 16:11:22".

What i got so far? This query:

where("short_response->>'#{item_id}' ~* ?", "\"value\": BETWEEN #{start_date} AND #{end_date}")

Thank you! Any help will be appreciated.

Aucun commentaire:

Enregistrer un commentaire