Support filtering on array column types

[magrinj]

Describe the feature request

This feature request aims to add support for filtering operations on array types in pg_graphql. Currently, pg_graphql doesn't support filter operations on any array types, including but not limited to enums and integers as first thought in this ticket.

Detailed Description

Array types are a common structure in PostgreSQL, and their utility within GraphQL queries is significant. The ability to filter these arrays directly within GraphQL queries would enhance the power and flexibility of the pg_graphql extension.

Example Use Case

Consider a table doc with an array of enums and an array of integers:

CREATE TYPE my_enum AS ENUM('VALUE_ONE', 'VALUE_TWO');

CREATE TABLE doc (
    id serial primary key,
    enumArr my_enum[],
    intArr int[] default '{1,2}'
);

The resulting GraphQL schema will be:

type Doc implements Node {
  """Globally Unique Record Identifier"""
  nodeId: ID!
  id: Int!
  enumArr: [MyEnum]
  otherArr: [Int]
}

input DocFilter {
  id: IntFilter
  nodeId: IDFilter

  """
  Returns true only if all its inner filters are true, otherwise returns false
  """
  and: [DocFilter!]

  """
  Returns true if at least one of its inner filters is true, otherwise returns false
  """
  or: [DocFilter!]

  """Negates a filter"""
  not: DocFilter
}

type Query {
  """A pagable collection of type `Doc`"""
  docCollection(
    """Query the first `n` records in the collection"""
    first: Int

    """Query the last `n` records in the collection"""
    last: Int

    """Query values in the collection before the provided cursor"""
    before: Cursor

    """Query values in the collection after the provided cursor"""
    after: Cursor

    """Filters to apply to the results set when querying from the collection"""
    filter: DocFilter

    """Sort order to apply to the collection"""
    orderBy: [DocOrderBy!]
  ): DocConnection
}

In the current setup, users are unable to filter these array fields using pg_graphql. For instance, filtering doc records where enumArr contains 'VALUE_ONE' or intArr contains the number 1 is not possible.

Expected Behavior

• Add filtering capabilities such as eq (equals) and contains for array fields.
• These filters should be applicable to all array types, including enums.

Versions:

• PostgreSQL: 14.9.1
• pg_graphql: 1.4.2

[martmull]

Hey @olirice, i have tested array type with

• postgreSQL: 15.6
• pg_graphql: 1.5.1

Array type are well supported in 1.5.1, but still not filtering or ordering operations on them. Is this normal or I am missing something? Cheers

[olirice]

not filtering or ordering operations on

Filtering is straightforward, we just need to do the work. Also a good first issue if anyone wants to take a swing at it. This is a similar PR implementing the nin filters https://github.com/supabase/pg_graphql/pull/344/files

I don't think it's likely that we'll implement ordering based on array types though, that doesn't feel intuitive to me. Please comment if you disagree

[martmull]

Regarding the filtering, it will indeed prove to be very beneficial for us.

Concerning the ordering, you are correct; it is challenging to ascertain the utility of array type sorting. However, given that this feature is present in PostgreSQL (https://www.postgresql.org/docs/9.4/functions-array.html), it could be advantageous in certain scenarios. For us, the primary concern is maintaining consistency across all the operations available for field types.