PostgreSQL - could not identify an equality operator for type json

Question

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?

Answer 1

Use jsonb_build_object. Notice the b for binary after json.

Answer 2

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

https://www.db-fiddle.com/f/vUBjUyKDUNLWzySHKCKcXA/1

Answer 3

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: https://github.com/rails/rails/issues/17706