CODEWITHSUNDEEP
postgresqljsonb
Lge24
Lge24

Reputation: 27

Postgres jsonb : cast array element to integer

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.

Upvotes: 1

Views: 1322

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28233

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

Upvotes: 2

Related Questions