Compare number of rows in result from subquery on json (Postgres)

Vote count:

0

I have a table called BusinessHours, which contains a json called hoursJSON, which consists of a "normalHours" and "holidayHours" key, each mapped to a nested json array.

I want to select businesses which have holiday periods containing today's date. So far I have something like:

SELECT 
/*some query logic here*/
WHERE SELECT COUNT(*) FROM 
    (SELECT * FROM json_array_elements(BusinessHours.hoursJSON->holidayHours) AS businessHolidayHours
     WHERE current_date BETWEEN businessHolidayHours->>'startDate' AND businessHolidayHours->>'endDate') 
> 0

What is the correct syntax to do this?

asked 2 mins ago

Teboto

3

Compare number of rows in result from subquery on json (Postgres)