英文:
how to find a record where you have to check inside a json object in an array field using postgresql?
问题
I see that you're having trouble with your SQL query. It seems you're trying to use the json_array_elements
function, but you're encountering an error. To resolve this issue, you can use the jsonb_array_elements
function instead, which is specifically designed for JSON arrays. Here's the modified query:
SELECT *
FROM programs
WHERE EXISTS (
SELECT *
FROM jsonb_array_elements(sessions) AS session
WHERE
session->>'day' = :day
AND :currentDate::time BETWEEN (session->>'start_time'::time - (entry_time_range || ' minutes')::interval)::time
AND (session->>'start_time'::time + (entry_time_range || ' minutes')::interval)::time
)
AND (filter IS NULL OR filter LIKE :schedule)
AND :currentDate BETWEEN start_line AND deadline
AND facility_id = :facility_id;
Make sure you replace json_array_elements
with jsonb_array_elements
in your query, and it should work correctly with your JSON array.
英文:
I want to select * programs which are today and they should have the same schedule or nothing. Each program has a session array with json objects, each json object has a start_time, a finish_time (like this 11:00:44) and a day ( ex. Monday).
I want to select the program which has a session object that has a day as currentDay and the currenttime between the start_time + programs.entry_time_range (which is 30 ) and start_time-programs.entry_time_range.
I write this query:
SELECT *
FROM programs
WHERE EXISTS (
SELECT *
FROM json_array_elements(sessions) AS session
WHERE
session->>'day' = :day
AND :currentDate::time BETWEEN (session->>'start_time'::time - (entry_time_range || ' minutes')::interval)::time
AND (session->>'start_time'::time + (entry_time_range || ' minutes')::interval)::time
)
AND( filter IS NULL
OR filter LIKE :schedule
)
AND :currentDate BETWEEN startline AND deadline
AND facility_id = :facility_id;`
but I cannot make it work, I got this error:
function json_array_elements(json[]) does not exist
I tried to cast to json object too but I got another error.
So how can I solve this? What should I do??
Here an example program table:
id | start_line | deadline | filters | facility_id | entry_time_range | sessions
---------------------------------------------------------------------------------
2 | 2023-04-03 03:00:00+03 | 2023-04-03 03:00:00+03 | {"in":[{"schedule":[["de682021-c99a-47a5-bc3a-8e089e033732"]]}]} | 'some_id' | 30 | {"{\"day\":\"Monday\",\"start_time\":\"06:00:00\",\"finish_time\":\"07:00:00\"}","{\"day\":\"Thursday\",\"start_time\":\"16:11:00\",\"finish_time\":\"23:00:00\"}"}
答案1
得分: 0
我可以访问数组中的JSON对象,使用 unnest 如下所示:
SELECT *
FROM programs
WHERE EXISTS (
SELECT 1
FROM unnest(sessions) AS session
WHERE session->>'day' = :day
AND :currentDate::time BETWEEN
((session->>'start_time')::time - (entry_time_range || ' minutes')::interval)::time
AND ((session->>'start_time')::time + (entry_time_range || ' minutes')::interval)::time
)
AND (filter IS NULL OR filter LIKE :schedule)
AND :currentDate BETWEEN startline AND deadline
AND facility_id = :facility_id;
英文:
I put my answer in case other would need.
I could access to the json objects inside the array using unnest as following:
SELECT *
FROM programs
WHERE EXISTS (
SELECT 1
FROM unnest(sessions) AS session
WHERE session->>'day' = :day
AND :currentDate::time BETWEEN
((session->>'start_time')::time - (entry_time_range || ' minutes')::interval)::time
AND ((session->>'start_time')::time + (entry_time_range || ' minutes')::interval)::time
)
AND (filter IS NULL OR filter LIKE :schedule)
AND :currentDate BETWEEN startline AND deadline
AND facility_id = :facility_id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论