mongodb-filter-to-postgres

This package converts MongoDB query filters into PostgreSQL WHERE clauses.
It's designed to be simple, secure, and free of dependencies.

Why Use This Package?

When filtering data based on user-generated inputs, you need a syntax that's both intuitive and reliable. MongoDB's query filter is an excellent choice because it's simple, widely understood, and battle-tested in real-world applications. Although this package doesn't interact with MongoDB, it uses the same syntax to simplify filtering.

Supported Features:

• Basics: $eq, $ne, $gt, $gte, $lt, $lte, $regex, $exists
• Logical operators: $and, $or, $not, $nor
• Array operators: $in, $nin, $elemMatch
• Field comparison: $field (see #difference-with-mongodb)

This package is intended for use with PostgreSQL drivers like github.com/lib/pq and github.com/jackc/pgx. However, it can work with any driver that supports the database/sql package.

Basic Usage:

Install the package in your project:

go get -u github.com/poki/mongodb-filter-to-postgres

Basic example:

import (
  "github.com/poki/mongodb-filter-to-postgres/filter"

  "github.com/lib/pq" // also works with github.com/jackc/pgx
)

func main() {
  // Create a converter with options:
  // - WithAllowAllColumns: allow all columns to be filtered.
  // - WithArrayDriver: to convert arrays to the correct driver type, required when using lib/pq
  converter, err := filter.NewConverter(filter.WithAllowAllColumns(), filter.WithArrayDriver(pq.Array))
	if err != nil {
		// handle error
	}

  // Convert a filter query to a WHERE clause and values:
  input := []byte(`{"title": "Jurassic Park"}`)
  conditions, values, err := converter.Convert(input, 1) // 1 is the starting index for params, $1, $2, ...
  if err != nil {
    // handle error
  }
  fmt.Println(conditions, values) // ("title" = $1), ["Jurassic Park"]

  db, _ := sql.Open("postgres", "...")
  db.QueryRow("SELECT * FROM movies WHERE " + conditions, values...)
}

(See examples/ for more examples)

Complex filter example:

This project was created and designed for the poki/netlib project, where we needed to convert complex filters from the API to SQL queries. The following example shows a complex filter and how it is converted to a WHERE clause and values:

{
  "$and": [
    {
      "$or": [                                // match two maps
        { "map": { "$regex": "aztec" } },
        { "map": { "$regex": "nuke" } }
      ]
    },
    { "password": "" },                       // no password set
    {
      "playerCount": { "$gte": 2, "$lt": 10 } // not empty or full
    }
  ]
}

Converts to:

(
  "customdata"->>"map" ~* $1
  OR
  "customdata"->>"map" ~* $2
)
AND "password" = $3
AND (
  "playerCount" >= $4
  AND
  "playerCount" < $5
)

values := []any{"aztec", "nuke", "", 2, 10}

(given "customdata" is configured with filter.WithNestedJSONB("customdata", "password", "playerCount"))

Order By Support

In addition to filtering, this package also supports converting MongoDB-style sort objects into PostgreSQL ORDER BY clauses using the ConvertOrderBy method:

// Convert a sort object to an ORDER BY clause
sortInput := []byte(`{"playerCount": -1, "name": 1}`)
orderBy, err := converter.ConvertOrderBy(sortInput)
if err != nil {
  // handle error
}
fmt.Println(orderBy) // "playerCount" DESC, "name" ASC

db.Query("SELECT * FROM games ORDER BY " + orderBy)

Sort Direction Values:

• 1: Ascending (ASC)
• -1: Descending (DESC)

Return value

The ConvertOrderBy method returns a string that can be directly used in an SQL ORDER BY clause. When the input is an empty object or nil, it returns an empty string. Keep in mind that the method does not add the ORDER BY keyword itself; you need to include it in your SQL query.

JSONB Field Sorting:

For JSONB fields, the package generates sophisticated ORDER BY clauses that handle both numeric and text sorting:

// With WithNestedJSONB("metadata", "created_at"):
sortInput := []byte(`{"score": -1}`)
orderBy, err := converter.ConvertOrderBy(sortInput)
// Generates: (CASE WHEN jsonb_typeof(metadata->'score') = 'number' THEN (metadata->>'score')::numeric END) DESC NULLS LAST, metadata->>'score' DESC NULLS LAST

This ensures proper sorting whether the JSONB field contains numeric or text values.

Tip

Always add an , id ASC to your ORDER BY clause to ensure a consistent order (where id is your primary key).

if orderBy != "" {
  orderBy += ", "
}
orderBy += "id ASC"

Difference with MongoDB

• The MongoDB query filters don't have the option to compare fields with each other. This package adds the $field operator to compare fields with each other.
  For example:

{
  "playerCount": { "$lt": { "$field": "maxPlayers" } }
}

• Some comparisons have limitations.>, >=, < and <= only work on non-jsob fields if they are numeric.

Contributing

If you have a feature request or discovered a bug, we'd love to hear from you! Please open an issue or submit a pull request. This project adheres to the Poki Vulnerability Disclosure Policy.

Main Contributors

• Koen Bollen
• Erik Dubbelboer