Use where queries in JSONB datatype in Rails Postgres

Question

How to query out schedules who has slot_details which have empty appointment_id. Here is an example data set:

    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>

I tried the example in guides as well. There is no example for where query on array of json. Please help.

possible duplicate of How to make Rails 4.2 work with Postgres Jsonb? — khampson
– khampson, Commented Dec 30, 2014 at 1:34
Any particular reason you're using JSON for this? Your slot_details looks to be very consistently structured, a separate table would probably be a better idea. — mu is too short
– mu is too short, Commented Dec 30, 2014 at 1:35
@muistooshort I decided to go with this structure because. The row you see is for a single day for a single consulting_location of a doctor. A doctor can has many such consulting_location each having multiple slots for a single day. If I bring this out as a seperate table, won't become too big very soon? — Aravind
– Aravind, Commented Dec 30, 2014 at 7:49
Also the question has not been answered before, I have tried the solution there and it was not helpful. There was no pointers as to how to query array of json. — Aravind
– Aravind, Commented Dec 30, 2014 at 14:39
How would it be bigger as a separate table than as embedded JSON? I'd guess that it would be smaller, faster, easier to work with, and have access to a richer set of data types. You could even set up proper integrity constraints. — mu is too short
– mu is too short, Commented Dec 30, 2014 at 17:57

PyWebDesign · Accepted Answer · 2015-04-23 13:34:20Z

0

Maybe this could help you:

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

the idea is to ask if the partial subarray contained. If you were not in an array bun in an object it would instead be (let say we have a profile object):

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

don't be fooled trying to use ? here instead of @>

answered Apr 23, 2015 at 13:34

PyWebDesign

3072 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

Use where queries in JSONB datatype in Rails Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related