"ERROR: could not identify an equality operator for type json" when using group by on a js-CodePudding

I am trying to run the following query: `

SELECT e.date as date, e.headers::json->'url' as url, e.time_spent as time_spent 
FROM some_table e 
  JOIN some_table a ON e.key= a.key 
WHERE a.name='firefox' 
AND e.date BETWEEN '2022-11-15' AND '2022-11-21' 
GROUP BY url 
ORDER BY time_spent 
DESC LIMIT 30;

The problem is with using GROUP BY url. With that in place the query returns the error:

ERROR: could not identify an equality operator for type json

Error Image Screenshot

CodePudding user response：

As documented in the manual the -> operator returns a JSON value which has no equality operator defined (so you can't compare json values using = which is needed for the GROUP BY.

You need to extract it as text using ->>

e.headers::json->>'url'