Pypika: Postgresql query array in array

Question

Want to generate below query using pypika for postgresql.

select name, employee_id 
from employee 
where phone_no && ARRAY['7377','3877','9277']::varchar[]

Is there any way we can achieve this?

Answer 1

Yes, surely you can generate this query using pypika for postgresql.Try this;

from pypika import QueryBuilder, Table

# Assuming "employee" is the name of your table
employee = Table('employee')

# Defining phone numbers array
phone_numbers = ['7377', '3877', '9277']

# Now Generating PyPika query
query = QueryBuilder().select(employee.name, employee.employee_id)\
    .from_(employee)\
    .where(employee.phone_no.like_any(phone_numbers))  # Using the like_any method for array containment

# Getting generated SQL query
sql_query = query.get_sql()

print(sql_query)

To see if any member in the phone_no array matches any element in the given phone_numbers array, I used the like_any method in this query.

Hope it's helpful :)