current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

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.

share|improve this question
    
possible duplicate of How to make Rails 4.2 work with Postgres Jsonb? – khampson Dec 30 '14 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 Dec 30 '14 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 Dec 30 '14 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 Dec 30 '14 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 Dec 30 '14 at 17:57

1 Answer 1

up vote 0 down vote

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 @>

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.