其中,使用Rails中的Postgres查询的JSONB数据类型 [英] Use where queries in JSONB datatype in Rails Postgres
本文介绍了其中,使用Rails中的Postgres查询的JSONB数据类型的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何查询出谁拥有具有空appointment_id slot_details时间表。下面是一个例子的数据集:
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.
推荐答案
这也许可以帮助你:
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"}'
不要上当试图用
在这里,而不是 @>
这篇关于其中,使用Rails中的Postgres查询的JSONB数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文