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?
24 Answers
Use jsonb_build_object. Notice the b for binary after json.
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 ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoa3FsYmWBdISOnKaupJRiu7DAjKKbnqaknrO6ecCnZJ6ppZa5qsDYZqapnaKWwbC%2BjJ%2Bmq2Wkrr2mecmspqdlp52yr3nUrKCnn12ZtrTAyKearQ%3D%3D
