Postgres jsonb : cast array element to integer

Question

Using Postgres 11.2.9 (ubuntu),

In my database, I have a jsonb field containing values that look like this : [1618171589133, 1618171589245, 1618171589689]

I'd like to retrieve rows where the first element is lower than a specific value. I've tried this :

SELECT * FROM user.times WHERE time ->> 0 < 1618171589133

but I get the following error : ERROR: operator does not exist: text = bigint

Should I somehow cast the time value to numeric value ? I've tried time ->> 0::numeric but I actually don't know what to do.

Have you tried time -> 0 < 1618171589133 ? A look here postgresql.org/docs/9.5/functions-json.html makes it look like ->> intentionally returns text instead of a numeric value — GSP
@GSP fair point, however I think I'd still have to cast it as bigint, time -> 0 < 1618171589133 returns an error operator does not exist: jsonb <= bigint — Louis

Haleemur Ali Haleemur Ali · Accepted Answer · 2020-02-14T13:57:22

The ->> operator returns the element at given position as text, which you can then convert to integer (or as it seems in this case, bigint), as you would normally do in postgres, using the :: as suffix.

SELECT * FROM user.times WHERE ((time ->> 0)::bigint) < 1618171589133

Postgres jsonb : cast array element to integer

1 Answers