postgresql - 使用不同时为 "could not identify an equality operator for type json"

Question

我有以下查询:

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; 

问题在于使用 distinct(date(survey_results.created_at))。有了那个地方查询返回错误:

could not identify an equality operator for type json

Here is db fiddle表明那个问题。我该如何解决？

Answer 1

使用jsonb_build_object。注意 json 之后的二进制文件 b。