# 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
}

      
        ?age=lt.13

# Arrays

Arrays are parsed depending on the used operator.

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

      
        ?id=in.(1,2,3)

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

      
        ?tags=cs.{example,new}

# Range Objects

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

      
        ?range=sl.[1,10)

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

      
        ?range=sl.(1,10]

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

# Column Conditions

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

      
        ?age=lt.13

# Logical Conjoining (AND)

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

      
        ?age=lt.13&student=is.true

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

      
        ?grade=gte.90&age=not.eq.14

# Logical Disjoining (OR)

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

      
        ?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,
  }
}

      
        ?or=(grade.eq.20,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
    }
  }
}

      
        ?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'
}

      
        ?my_tsv=fts(french).amusant

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

# Selecting

const query = {
  select: '*'
}

      
        ?select=*

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

      
        ?select=first_name,age

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

      
        ?select=first_name,age

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

      
        ?select=first_name

# Renaming Columns

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

      
        ?select=firstName:first_name,age

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

      
        ?select=firstName:first_name

# Casting Columns

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

      
        ?select=full_name,salary::text

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

      
        ?select=full_name,salary::text

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

      
        ?select=full_name,salary::text

# JSON Columns

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

      
        ?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
  }
}

      
        ?select=id,json_data->blood_type,json_data->phones&json_data->age=gt.20

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

      
        ?select=id,json_data->phones->0->number

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
    }
  }
}

      
        ?select=id,jd:json_data,json_data->blood_type,json_data->phones

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

      
        ?select=id,json_data::json,json_data->blood_type,json_data->phones

# Ordering PostgREST docs (opens new window)

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

      
        ?order=age.desc,height.asc

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

      
        ?order=age.desc,height.asc

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

      
        ?order=age.desc,height.asc

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

      
        ?order=age.desc,height.asc.nullslast

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

      
        ?order=age

# Limits and Pagination PostgREST docs (opens new window)

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

      
        ?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
      }
    }
  }
}

      
        ?select=title,directors(id,last_name)

Aliases

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

      
        ?select=title,director:directors(id,last_name)

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'
    },
    'central_addresses!billing_address': {
      select: '*'
    }
  },
  '91_comps.year.eq': 1991
}

      
        ?select=*,actors(*),91_comps:competitions(name),central_addresses!billing_address(*)&91_comps.year=eq.1991&actors.order=last_name,first_name&actors.limit=10&actors.offset=2&actors.character=in.(Chico,Harpo,Groucho)

# Insertions / Updates PostgREST docs (opens new window)

# Columns

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

      
        ?columns=source,publication_date,figure

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

      
        ?columns=source,publication_date,figure

# On Conflict

const query = {
  on_conflict: 'source'
}

      
        ?on_conflict=source

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

      
        ?on_conflict=source,publication_date,figure