Postgresql：使用jsonb字段查询时间范围之间的查询

Question

我有一张包含两个字段的表：

id(serial), data(jsonb)

我有一些数据记录，其中包含一个DateTime字段，以UNIX时间戳的形式存储：

{"Device":132,"Datetime": 1434166552,...}

我正在尝试在范围之间进行查询：

SELECT *
FROM trips
WHERE data->>'Datetime' BETWEEN
    EXTRACT(EPOCH FROM date '2014-04-01') AND
    EXTRACT(EPOCH FROM date '2014-04-15' + interval '1 day')
    AND id = 123

消息

ERROR:  operator does not exist: text >= double precision
LINE 3: WHERE data->>'Datetime' BETWEEN

我做错了什么事情，请有经验的人帮助我解决一下？谢谢。

- Goku

Answer 1

->> 操作符返回一个 JSON 对象字段作为 text（详见此处）。您需要将其转换为所需类型：

SELECT *
FROM trips
WHERE (data->>'Datetime')::int 
  BETWEEN EXTRACT(EPOCH FROM date '2014-04-01') 
      AND EXTRACT(EPOCH FROM date '2014-04-15' + interval '1 day')
  AND id = 123

- Marth

---

为什么不使用 data::json->>'Datetime' ? - Onel Sarmiento

我不确定我理解了。data已经是一个json(或者jsonb，但这里没有任何变化)，那么::json会做什么？ - Marth

我以为它会返回整数，因为 ->> 是整型？ - Onel Sarmiento

json/jsonb运算符->>返回一个text（请参见答案中的链接）。 - Marth