The problem is with using distinct(date(survey_results.created_at))

No. The problem is with using DISTINCT in that it is not a function. It always applies to all columns of the result. distinct(a), b is the same as distinct a, (b) or distinct a, b. And because of that, distinct tries to compare identical values of your second column which is of type json and can't be compared with =

If you just want the "latest" value, you can do this with Postgres' distinct on () operator:

SELECT distinct on (date(survey_results.created_at)) 
       date(survey_results.created_at) as date,
       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 distinct on () combined with order by picks the first row for subsequent identical values of the column(s) specified in the ON () part. In this case it would return the earliest date. If you want the "latest" row, change the sort order to desc

Migrate to using JSONB and you won't have this issue.

This is the standard piece of advice followed a few years ago when Postgres 9.4 was coming out. Here is a thread in the Ruby on Rails community that describes migrating to JSONB as the solution.

Here is the thread:

Facing a similar issue while working on the below SQL, we could able to fix it by explicitly casting JSON extracted column to text. as below

select 
b.booking_id as booking_id, 
(p.product_meta_data::json->'confirmation_window')::text as confirmation_window, 
bi.product_id as product_id 
from bookings b 
left join booking_items bi on b.booking_id = bi.booking_id 
left join products p on p.product_id = bi.product_id