filtering

README

Filtering

The filtering package provides an easy way to convert Common Expression Language (CEL) expressions into SQL WHERE clauses. This is useful when you want to filter data in a database based on user input.

The package supports most of the AIP-160 CEL functions and operators. Unsupported functions/operators include:

• The negation(-) operator. The NOT operator should be used instead.
• The has(:) operator. The IN operator should be used instead.
• The wildcard(*) operator.

Usage

Create a new Filter instance using NewFilter

    filter, err := filtering.NewFilter()

Use the Parse method to convert a CEL expression into a SQL WHERE clause

    stmt, err := filter.Parse("age > 18")

You can optionally pass in Identifiers to the NewFilter method. Identifiers are used to declare common protocol buffer types for conversion. Common identifiers are for google.protobuf.Timestamp, google.protobuf.Duration, google.type.Date etc. They enable to parser to recognize protocol buffer types and convert them to SQL data types.

    filter, err := filtering.NewFilter(filtering.Timestamp("Proto.create_time"),filtering.Duration("Proto.duration"))

Reserved keywords

One of your columns may have a reserved keyword as a name. For this you can register the column using the filtering.Reserved() identifier.

    filter, err := filtering.NewFilter(filtering.Reserved("Group"), filtering.Reserved("Lookup"))

Supported protobuf functions

Please note that the package only supports the following protobuf functions at the moment:

Timestamp

The timestamp function converts a string into a timestamp. Should be an RFC 3339(YYYY-MM-DDTHH:MM:SS[.ssssss][±HH:MM | Z]) timestamp string. e.g. 2006-01-02T15:04:05Z07:00

    stmt, err := filter.Parse("Proto.create_time > timestamp('2021-01-01T00:00:00Z')")

Native spanner TIMESTAMP data type columns should not use this function. Instead just a RFC 3339 timestamp string should be used.

    stmt, err := filter.Parse("create_time > '2021-01-01T00:00:00Z'")

Duration

The duration function converts a string into a duration. Should be a valid duration string. e.g. 1h, 1m, 1s

    stmt, err := filter.Parse("Proto.duration > duration('1h')")

Date

The date function converts a string into a date. Should be an ISO 8601(YYYY-MM-DD) date string.

    stmt, err := filter.Parse("Proto.date > date('2021-01-01')")

Native spanner DATE data type columns should not use this function. Instead just a ISO 8601 date string should be used.

    stmt, err := filter.Parse("effective_date > '2021-01-01'")

Other Supported functions

The package also supports the following scalar column/field functions:

Prefix

The prefix function checks if a string column starts with a given prefix.

    stmt, err := filter.Parse("prefix(key, 'resources/ABC')")

Suffix

The suffix function checks if a string column ends with a given suffix.

    stmt, err := filter.Parse("suffix(key, 'ABC')")

IN

The IN function checks if a column value is in a list of values.

    stmt, err := filter.Parse("key IN ['value1', 'value2']")

Documentation

Index

• type ErrInvalidFilter
  • func (e ErrInvalidFilter) Error() string
  • func (e ErrInvalidFilter) GRPCStatus() *status.Status
  • func (e ErrInvalidFilter) Is(target error) bool
• type ErrInvalidIdentifier
  • func (e ErrInvalidIdentifier) Error() string
  • func (e ErrInvalidIdentifier) GRPCStatus() *status.Status
  • func (e ErrInvalidIdentifier) Is(target error) bool
• type Filter
  • func NewFilter(identifiers ...Identifier) (*Filter, error)
  • func (f *Filter) DeclareIdentifier(identifier Identifier) error
  • func (f *Filter) Parse(filter string) (*spanner.Statement, error)
• type Identifier
  • func Date(path string) Identifier
  • func Duration(path string) Identifier
  • func Reserved(name string) Identifier
  • func Timestamp(path string) Identifier

Types

type ErrInvalidFilter

type ErrInvalidFilter struct {
	// contains filtered or unexported fields
}

func (ErrInvalidFilter) Error

func (e ErrInvalidFilter) Error() string

func (ErrInvalidFilter) GRPCStatus

func (e ErrInvalidFilter) GRPCStatus() *status.Status

func (ErrInvalidFilter) Is

func (e ErrInvalidFilter) Is(target error) bool

type ErrInvalidIdentifier

type ErrInvalidIdentifier struct {
	// contains filtered or unexported fields
}

func (ErrInvalidIdentifier) Error

func (e ErrInvalidIdentifier) Error() string

func (ErrInvalidIdentifier) GRPCStatus

func (e ErrInvalidIdentifier) GRPCStatus() *status.Status

func (ErrInvalidIdentifier) Is

func (e ErrInvalidIdentifier) Is(target error) bool

type Filter

type Filter struct {
	// contains filtered or unexported fields
}

Filter is a CEL filter expression to Spanner query parser.

It is used to parse a CEL filter expression and convert it to a Spanner statement.

func NewFilter

func NewFilter(identifiers ...Identifier) (*Filter, error)

NewFilter creates a new Filter instance with the given identifiers.

Identifiers are used to declare common protocol buffer types for conversion. Common identifiers are Timestamp, Duration, Date etc.

func (*Filter) DeclareIdentifier

func (f *Filter) DeclareIdentifier(identifier Identifier) error

DeclareIdentifier declares a new Identifier in the environment.

This is useful when you want to add a new identifier to the environment after creating the Filter instance.

May return an ErrInvalidIdentifier error if the identifier is invalid.

func (*Filter) Parse

func (f *Filter) Parse(filter string) (*spanner.Statement, error)

Parse parses a CEL filter expression and returns a Spanner statement.

Examples:

filter.Parse("Proto.effective_date.year > 2021 AND create_time > timestamp('2021-01-01T00:00:00Z') OR expire_after > duration('1h')")
filter.Parse("key = 'resources/1' OR Proto.effective_date = date('2021-01-01')")
filter.Parse("Proto.state = 'ACTIVE'"
filter.Parse("key IN ['resources/1', 'resources/2']")
filter.Parse("effective_date != null)
filter.Parse("count >= 10)

May return an ErrInvalidFilter error if the filter is invalid.

type Identifier

type Identifier interface {
	Path() string
	// contains filtered or unexported methods
}

Identifier represents a CEL type identifier for certain types.

Commonly used for identifying and transforming protocol buffer types. For example:

• Timestamp() will convert a google.protobuf.Timestamp to a spanner Timestamp type.
• Duration() will convert a google.protobuf.Duration to a spanner String type.

func Date

func Date(path string) Identifier

Date enables conversion of google.type.Date to a spanner String/Date type.

It takes in the path to the column/field.

Example:

Date("effective_date")
Date("Proto.effective_date")

func Duration

func Duration(path string) Identifier

Duration enables conversion of google.protobuf.Duration to a spanner int type.

It takes in the path to the column/field.

Example:

Duration("expire_after")
Duration("Proto.expire_after")

func Reserved

func Reserved(name string) Identifier

Reserved allows for the querying of columns with reserved keywords. It instructs the parser to wrap the column names with backticks(`). This is only required if you have a column with a reserved keyword

It takes in the name of the column.

func Timestamp

func Timestamp(path string) Identifier

Timestamp enables conversion of google.protobuf.Timestamp to a spanner String/Timestamp type.

It takes in the path to the column/field.

Example:

Timestamp("create_time")
Timestamp("Proto.create_time")