Query

The Query class converts an input object into a PostgREST query string.

By default, all values are converted to their appropriate representation for PostgREST query strings.

Values & Operators

Undefined

Undefined values are excluded from the query string.

const query = {
  'age.lt': 13,
  'grade.gte': undefined
}

Arrays

Arrays are parsed depending on the used operator.

const query = {
  'id.in': [1, 2, 3]
}

const query = {
  'tags.cs': ['example', 'new']
}

Range Objects

const query = {
  'range.sl': { lower: 1, upper: 10 }
}

const query = {
  'range.sl': { lower: 1, includeLower: false, upper: 10, includeUpper: true }
}

Horizontal Filtering (Rows) PostgREST docs (opens new window)

Column Conditions

const query = {
  'age.lt': 13
}

Logical Conjoining (AND)

const query = {
  'age.lt': 13,
  'student.is': true
}

const query = {
  'grade.gte': 90,
  'age.not.eq': 14
}

Logical Disjoining (OR)

const query = {
  or: {
    'grade.gte': 20,
    'age.lte': 30,
  }
}

For setting two conditions on the same column, use aliases - these are stripped before creating the query string.

const query = {
  or: {
    '0:grade.eq': 20,
    '1:grade.eq': 50,
  }
}

Negated logical operators and nesting:

const query = {
  and: {
    'grade.gte': 90,
    'student.is': true,
    'not.or': {
      'age.eq': 14,
      'age.not.is': null
    }
  }
}

Full-Text Search

const query = {
  'my_tsv.fts(french)': 'amusant'
}

Vertical Filtering (Columns) PostgREST docs (opens new window)

Selecting

const query = {
  select: '*'
}

const query = {
  select: 'first_name,age'
}

const query = {
  select: ['first_name', 'age']
}

const query = {
  select: {
    'first_name': true,
    // NOTE: falsy values are ignored!
    age: 0
  }
}

Renaming Columns

const query = {
  select: ['firstName:first_name', 'age']
}

const query = {
  select: {
    'firstName:first_name': true
  }
}

Casting Columns

const query = {
  select: ['full_name', 'salary::text']
}

const query = {
  select: {
    'full_name': true,
    salary: 'text'
  }
}

const query = {
  select: {
    'full_name': true,
    salary: {
      '::': 'text'
    }
  }
}

JSON Columns

const query = {
  select: ['id', 'json_data->>blood_type', 'json_data->phones']
}

TIP

If a field of the select object has a select key itself, it is handled as an embed, otherwise as a JSON field.

const query = {
  select: {
    id: true,
    json_data: {
      blood_type: true,
      phones: true
    }
  },
  // Nested filter on JSON column
  json_data: {
    'age.gt': 20
  }
}

const query = {
  select: {
    id: true,
    json_data: {
      phones: [
        {
          number: true
        }
      ]
    }
  }
}

If a JSON column is aliased or cast in object syntax, the json_data field is added to the query string. E.g.:

const query = {
  select: {
    id: true,
    'jd:json_data': {
      blood_type: true,
      phones: true
    }
  }
}

const query = {
  select: {
    id: true,
    json_data: {
      '::': 'json',
      blood_type: true,
      phones: true
    }
  }
}

Ordering PostgREST docs (opens new window)

const query = {
  order: 'age.desc,height.asc'
}

const query = {
  order: ['age.desc', 'height.asc']
}

const query = {
  order: [
    ['age', 'desc'],
    ['height', 'asc']
  ]
}

const query = {
  order: {
    age: 'desc',
    height: 'asc.nullslast'
  }
}

const query = {
  order: {
    age: true
  }
}

Limits and Pagination PostgREST docs (opens new window)

const query = {
  limit: 1,
  offset: 10
}

Resource Embedding PostgREST docs (opens new window)

TIP

If a field of the select object has a select key itself, it is handled as an embed, otherwise as a JSON field.

Simple

const query = {
  select: {
    title: true,
    directors: {
      select: {
        id: true,
        last_name: true
      }
    }
  }
}

Aliases

const query = {
  select: {
    title: true,
    'director:directors': {
      select: {
        id: true,
        last_name: true
      }
    }
  }
}

Full Example

const query = {
  select: {
    '*': true,
    actors: {
      select: '*',
      order: ['last_name', 'first_name'],
      'character.in': ['Chico', 'Harpo', 'Groucho'],
      limit: 10,
      offset: 2
    },
    '91_comps:competitions': {
      select: 'name',
      'year.eq': 1991
    },
    'central_addresses!billing_address': {
      select: '*'
    }
  }
}

Insertions / Updates PostgREST docs (opens new window)

Columns

const query = {
  columns: 'source,publication_date,figure'
}

const query = {
  columns: ['source', 'publication_date', 'figure']
}

On Conflict

const query = {
  on_conflict: 'source'
}

const query = {
  on_conflict: ['source', 'publication_date', 'figure']
}