ERROR: malformed array literal in PostgreSQL

Question

I want filter on integer array in postgresql but when I am executing below query its giving me malformed array literal error.

select * from querytesting where 1111111111 = any((jsondoc->>'PhoneNumber')::integer[]);

Open image for reference- https://i.sstatic.net/Py3Z2.png

Answer 1

any(x) wants a PostgreSQL array as x. (jsondoc->>'PhoneNumber'), however, is giving you a text representation of a JSON array. A PostgreSQL array would look like this as text:

'{1,2,3}'

but the JSON version you get from ->> would look like:

'[1,2,3]'

You can't mix the two types of array.

You could use a JSON operator instead:

jsondoc->'PhoneNumber' @> 1111111111::text::jsonb

Using -> instead of ->> gives you a JSON array rather than text. Then you can see if the number you're looking for is in that array with @>. The double cast (::text::jsonb) is needed to convert the PostgreSQL number to a JSON number for the @> operator.

---

As an aside, storing phone numbers as numbers might not be the best idea. You don't do arithmetic on phone numbers so they're not really numbers at all, they're really strings that contain digit characters. Normalizing the phone number format to international standards and then treating them as strings will probably serve you better in the long term.