"could not identify an equality operator for type json" when using distinct

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?

distinct 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 = — user330315
– user330315, Commented Jan 24, 2018 at 10:43
Got it, this query returns a duplicated dates, how can I fix it without using distinct? — Mateusz Urbański
– Mateusz Urbański, Commented Jan 24, 2018 at 10:45

Clodoaldo Neto · Accepted Answer · 2018-01-24 10:48:39Z

79

Use jsonb_build_object. Notice the b for binary after json.

answered Jan 24, 2018 at 10:48

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Yair V. Over a year ago

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

Eduardo Mior Over a year ago

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 Over a year ago

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 Over a year ago

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

MoxJet Oct 24 at 17:05

There is also a similar function for json_agg called jsonb_agg

Paweł Gościcki · Accepted Answer · 2022-05-10 12:08:46Z

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

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

Nitrodist · Accepted Answer · 2022-01-08 04:20:40Z

9

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

answered Jan 8, 2022 at 4:20

Nitrodist

1,7955 gold badges25 silver badges36 bronze badges

Comments

Kamal · Accepted Answer · 2023-01-03 09:51:54Z

2

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

answered Jan 3, 2023 at 9:51

Kamal

296 bronze badges

Comments

Tomasz · Accepted Answer · 2024-07-23 11:30:56Z

0

To use distinct you need to change column from json to jsonb.

Described in article : https://github.com/filamentphp/filament/issues/8698

"the solution is to switch to a jsonb column. The distinct() part of the query does not come from the Filament codebase (as far as I can see), so there is not anything we can change about it."

answered Jul 23, 2024 at 11:30

Tomasz

1,45413 silver badges14 bronze badges

Collectives™ on Stack Overflow

"could not identify an equality operator for type json" when using distinct

5 Answers 5

5 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related