In addition to the expressions listed in the Ash expressions guide, AshPostgres provides the following expressions

Fragments

Fragments allow you to use arbitrary postgres expressions in your queries. Fragments can often be an escape hatch to allow you to do things that don't have something officially supported with Ash.

Examples

Simple expressions

fragment("? / ?", points, count)

Calling functions

fragment("repeat('hello', 4)")

Using entire queries

fragment("points > (SELECT SUM(points) FROM games WHERE user_id = ? AND id != ?)", user_id, id)

In calculations

calculations do
  calculate :lower_name, :string, expr(
    fragment("LOWER(?)", name)
  )
end

In migrations

create table(:managers, primary_key: false) do
  add :id, :uuid, null: false, default: fragment("UUID_GENERATE_V4()"), primary_key: true
end

Escaping question marks

In fragments, ? is a placeholder for an interpolated argument. If your SQL needs a literal ? — for example, the JSONB key-exists operator ? or the jsonb_path_exists ? pattern — escape it as \\?:

# JSONB key-exists: does `data` have key "foo"?
fragment("? \\? 'foo'", data)

# jsonb_path_exists with `?` filter
fragment("jsonb_path_exists(plan_snapshot->'steps', '$ \\? (@.role == \"coach\")')")

An unescaped ? that has no matching argument produces the error fragment(...) expects extra arguments in the same amount of question marks in string.

JSONB

You can combine multiple fragment/1 calls with boolean operators in a single expr:

Ash.Query.filter(
  Plan,
  expr(
    fragment("plan_snapshot->'states'->-1->>'state' = 'read_only'")
    and fragment("jsonb_path_exists(plan_snapshot->'steps', '$ \\? (@.role == \"coach\" && @.state == \"ready\")')")
  )
)

Remember to escape any literal ? as \\? (see Escaping question marks above) — this is the most common source of confusion when filtering JSONB.

For accessing JSONB fields with the -> and ->> operators, interpolate the column with ?:

fragment("?->>'name' = ?", data, ^name)

Like and ILike

These wrap the postgres builtin like and ilike operators.

Please be aware, these match patterns not raw text. Use contains/1 if you want to match text without supporting patterns, i.e % and _ have semantic meaning!

For example:

Ash.Query.filter(User, like(name, "%obo%")) # name contains obo anywhere in the string, case sensitively

Ash.Query.filter(User, ilike(name, "%ObO%")) # name contains ObO anywhere in the string, case insensitively

Trigram similarity

To use this expression, you must have the pg_trgm extension in your repos installed_extensions list.

This calls the similarity function from that extension. See more in the pgtrgm guide

For example:

Ash.Query.filter(User, trigram_similarity(first_name, "fred") > 0.8)