如何在PostgreSQL中将tsrange列查询为JSON格式？

问题场景 #

需要将tsrange类型的reserved列以JSON格式返回，执行带DISTINCT的查询时报错，但单独转换单个tsrange值时正常：

查询一（报错） #

-- query one
SELECT DISTINCT c.*, to_json(a.reserved) FROM complete_reservations c
JOIN availability a ON (c.id = a.reservationid)

报错信息：

ERROR:  could not identify an equality operator for type json
LINE 1: SELECT DISTINCT c.*, to_json(a.reserved) FROM complete_reser...
                             ^
SQL state: 42883
Character: 22

查询二（正常） #

-- query two
SELECT to_json('[2011-01-01,2011-03-01)'::tsrange);

返回结果：

"[\"2011-01-01 00:00:00\",\"2011-03-01 00:00:00\")"

差异原因 #

查询二没有使用DISTINCT，不需要对返回列做相等性校验；而查询一的DISTINCT要求PostgreSQL对所有返回列（包括to_json(a.reserved)生成的json类型列）执行相等判断，但PostgreSQL的json类型没有内置的相等运算符，因此触发报错。

解决方法 #

可以通过以下几种方式让查询一正常工作：

方法1：将JSON结果转为TEXT类型 #

TEXT类型有内置的相等运算符，DISTINCT可以正常处理：

SELECT DISTINCT c.*, to_json(a.reserved)::text FROM complete_reservations c
JOIN availability a ON (c.id = a.reservationid)

方法2：改用jsonb类型转换 #

jsonb类型支持相等运算，使用to_jsonb替代to_json即可：

SELECT DISTINCT c.*, to_jsonb(a.reserved) FROM complete_reservations c
JOIN availability a ON (c.id = a.reservationid)

方法3：用GROUP BY替代DISTINCT #

如果complete_reservations表有主键（比如id），可以通过分组来实现去重，避免DISTINCT对JSON列的校验：

-- PostgreSQL 10+ 支持仅按主键分组，自动包含其他列
SELECT c.*, to_json(a.reserved) FROM complete_reservations c
JOIN availability a ON (c.id = a.reservationid)
GROUP BY c.id;

如果是低于10的版本，需要显式列出c.*中的所有列：

SELECT c.*, to_json(a.reserved) FROM complete_reservations c
JOIN availability a ON (c.id = a.reservationid)
GROUP BY c.id, c.column1, c.column2, ..., a.reserved;

内容的提问来源于stack exchange，提问作者gaugau