I have the following query:
SELECT
distinct(date(survey_results.created_at)),
json_build_object(
'high',
ROUND(
COUNT(*) FILTER (WHERE ( scores#>>'{medic,categories,motivation}' in('high', 'medium'))) OVER(order by date(survey_results.created_at) ) * 1.0 /
(
CASE (COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('high','medium','low'))) OVER(order by date(survey_results.created_at)))
WHEN 0.0 THEN 1.0
ELSE (COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('high','medium','low'))) OVER(order by date(survey_results.created_at)))
END)* 100, 2 ) ) AS childcare FROM survey_results GROUP BY date, scores ORDER BY date asc;
The problem is with using distinct(date(survey_results.created_at))
. With that in place query returns error:
could not identify an equality operator for type json
Here is db fiddle that show that problem. How can I fix that?
Use jsonb_build_object
. Notice the b
for binary after json
.