如何在使用PostgreSQL时查找包含在数组字段中的JSON对象内部的记录?

huangapple go评论63阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年4月13日 22:26:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76006614.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定