我有一个部门表,其中有一个jsonb字段,该字段提供了工作时间,看起来像这样:
{
"Friday": {
"closeTime": "1970-01-01T17:00:00.000Z",
"isOpen": true,
"isOpen24Hours": false,
"openTime": "1970-01-01T08:00:00.000Z"
},
"Monday": {
"isOpen": false
},
"Saturday": {
"isOpen": false
},
"Sunday": {
"isOpen": false
},
"Thursday": {
"closeTime": "1970-01-01T17:00:00.000Z",
"isOpen": true,
"isOpen24Hours": false,
"openTime": "1970-01-01T08:00:00.000Z"
},
"Tuesday": {
"closeTime": "1970-01-01T17:00:00.000Z",
"isOpen": true,
"isOpen24Hours": false,
"openTime": "1970-01-01T08:00:00.000Z"
},
"Wednesday": {
"closeTime": "1970-01-01T17:00:00.000Z",
"isOpen": true,
"isOpen24Hours": false,
"openTime": "1970-01-01T08:00:00.000Z"
}
}
我还有另一个表,将一天的某些活动与一个部门关联起来,我想知道在活动发生的当天该部门是否开放.那么如何使用另一个表中的日期的星期几来查询这个jsonb字段呢?
我try 过这样的事情:
select a.activity_date, a.department_id,
d.hours_of_operation->to_char(a.activity_date, 'Day')->>'isOpen' as isOpen
from activity a
join department d on a.department_id = d.id
但只有当activity_Date是星期三(这是hours_of_operation jsonb中的最后一个键)时才返回isOpen值