How to SELECT a postgresql tsrange column as json?-CodePudding

I want to get a tsrange column returned as json but do not understand how get it to work in my query:

reserved is of type TSRANGE. c.id and a.reservationid are of type TEXT.

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

throws

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

it works if i try it like

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

Result: "[\"2011-01-01 00:00:00\",\"2011-03-01 00:00:00\")" and I do not understand the difference between both scenarios.

How do I get query one to behave like query two?

CodePudding user response：

The difference between the two queries is that in query one, the "a.reserved" column is of type tsrange, while in query two, the input to the to_json function is explicitly cast to a tsrange type. To make query one behave like query two, you can cast the "a.reserved" column to tsrange before passing it to the to_json function:

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

This should resolve the error and allow the query to return the JSON representation of the tsrange column.

CodePudding user response：

as pointed out in this comment by Edouard, there seems to be no JSON representation of a tsrange. Therefore I framed my question wrong.

In my concrete case it was sufficent to turn the tsrange to an array of the upper() and lower() values of the TSRANGE and cast those values as strings. this way i can use the output as is and let my downstream tools handle them as json.

SELECT DISTINCT c.*, ARRAY[to_char(lower(a.reserved),'YYYY-MM-DD HH:MI:SS'),to_char(upper(a.reserved),'YYYY-MM-DD HH:MI:SS')] reserved FROM complete_reservations_with_itemidArray c
JOIN availability a ON (c.id = a.reservationid)

which returns a value like this in the reserved column: {"2023-02-04 04:57:00","2023-02-05 04:57:00"} which can be parsed as json if needed.

I post this for reference. I am not sure if it exactly answers my question as it was framed.