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:

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

How can I fix that?

Solution 1

Use jsonb_build_object. Notice the b for binary after json.

Solution 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

Comments

[Surya]

Can we not do group by date(suryve_results.created_at)? @a_horse_with_no_name

[Yair V.]

to_jsonb helped me. The important thing is the fact that the jsonb type does have an equality operator.

[Eduardo Mior]

I was trying to make a UNION with JSON and was getting this message. I switched from JSON to JSONB and it worked fine. Thanks.

[ADJenks]

I discovered a similar error trying to union json_build_array() with another json_build_array() and finding it wouldn't work, then I found a similar solution switching to jsonb functions or casting to jsonb. I'm curious why there was no operator.

[ADJenks]

I understand now after running this query. It's questionable what it means to be equal for json. SELECT LENGTH('{"a":1,"a":2}'::json::text), LENGTH('{"a":1,"a":2}'::jsonb::text), LENGTH('{ "a":1}'::json::text), LENGTH('{ "a":1}'::jsonb::text) A similar comparison is shown here: stackoverflow.com/a/33731703/5078765