ruby-on-rails - 在 Rails Postgres 的 JSONB 数据类型中使用 where 查询

Question

如何查询出具有空 appointment_id 的 slot_details 的时间表。这是一个示例数据集:

    id: 98,
  consulting_location_doctor_id: 498,
  schedule_date: Thu, 15 Jan 2015 00:00:00 IST +05:30,
  slot_details:
   [{"end"=>"2015-01-15T15:00:00.000+00:00", "start"=>"2015-01-15T14:30:00.000+00:00", "title"=>" 2:30 pm to  3:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T15:30:00.000+00:00", "start"=>"2015-01-15T15:00:00.000+00:00", "title"=>" 3:00 pm to  3:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T16:00:00.000+00:00", "start"=>"2015-01-15T15:30:00.000+00:00", "title"=>" 3:30 pm to  4:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T16:30:00.000+00:00", "start"=>"2015-01-15T16:00:00.000+00:00", "title"=>" 4:00 pm to  4:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T17:00:00.000+00:00", "start"=>"2015-01-15T16:30:00.000+00:00", "title"=>" 4:30 pm to  5:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T17:30:00.000+00:00", "start"=>"2015-01-15T17:00:00.000+00:00", "title"=>" 5:00 pm to  5:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T18:00:00.000+00:00", "start"=>"2015-01-15T17:30:00.000+00:00", "title"=>" 5:30 pm to  6:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T18:30:00.000+00:00", "start"=>"2015-01-15T18:00:00.000+00:00", "title"=>" 6:00 pm to  6:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T19:00:00.000+00:00", "start"=>"2015-01-15T18:30:00.000+00:00", "title"=>" 6:30 pm to  7:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T19:30:00.000+00:00", "start"=>"2015-01-15T19:00:00.000+00:00", "title"=>" 7:00 pm to  7:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T20:00:00.000+00:00", "start"=>"2015-01-15T19:30:00.000+00:00", "title"=>" 7:30 pm to  8:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T20:30:00.000+00:00", "start"=>"2015-01-15T20:00:00.000+00:00", "title"=>" 8:00 pm to  8:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T21:00:00.000+00:00", "start"=>"2015-01-15T20:30:00.000+00:00", "title"=>" 8:30 pm to  9:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T21:30:00.000+00:00", "start"=>"2015-01-15T21:00:00.000+00:00", "title"=>" 9:00 pm to  9:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T22:00:00.000+00:00", "start"=>"2015-01-15T21:30:00.000+00:00", "title"=>" 9:30 pm to 10:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T22:30:00.000+00:00", "start"=>"2015-01-15T22:00:00.000+00:00", "title"=>"10:00 pm to 10:30 pm", appointment_id: ""},
    {"end"=>"2015-01-15T23:00:00.000+00:00", "start"=>"2015-01-15T22:30:00.000+00:00", "title"=>"10:30 pm to 11:00 pm", appointment_id: ""},
    {"end"=>"2015-01-15T23:30:00.000+00:00", "start"=>"2015-01-15T23:00:00.000+00:00", "title"=>"11:00 pm to 11:30 pm", appointment_id: ""},
    {"end"=>"2015-01-16T00:00:00.000+00:00", "start"=>"2015-01-15T23:30:00.000+00:00", "title"=>"11:30 pm to 12:00 am", appointment_id: ""}],
  start_and_end_time: Thu, 15 Jan 2015...Fri, 16 Jan 2015,
  deleted_at: nil,
  deleted_by_id: nil,
  created_at: Tue, 30 Dec 2014 04:28:06 IST +05:30,
  updated_at: Tue, 30 Dec 2014 04:28:06 IST +05:30>

我尝试了 guides 中的示例以及。没有在 json 数组上查询的示例。请帮忙。

Answer 1

也许这可以帮助你:

slot -> 'slot_details' @> '[{"end"=>"2015-01-15T20:00:00.000+00:00"}]'

想法是询问部分子数组是否包含。如果您不在对象的数组 bun 中，那么它会是(假设我们有一个配置文件对象):

profile -> 'basics' @> '{"name":"john"}'

不要被愚弄尝试在这里使用 ? 而不是 @>