Skip to main content

FilterObject Examples

Examples of FilterObject specifications for each operator of the filter grammar of the MariaDB REST Service, from sorting and equality to ranges, null checks, and combined conditions.

The following examples show a FilterObject for each property and operator of the FilterObject grammar of the MariaDB REST Service (MRS). Pass the FilterObject in the q parameter of a query, as described in Filtering in REST Queries.

Order By Property ($orderby)​

Order By With Literals​

{
  "$orderby": {"SALARY":  "ASC","ENAME":"DESC"}
}

Order By With Numbers​

{
  "$orderby": {"SALARY":  -1,"ENAME":  1}
}

As Of Property ($asof)​

With SCN (Implicit)​

{
  "$asof": 1273919
}

With SCN (Explicit)​

{
  "$asof": {"$scn": "1273919"}
}

With Date (Implicit)​

{
  "$asof": "2014-06-30T00:00:00Z"
}

With Date (Explicit)​

{
  "$asof": {"$date": "2014-06-30T00:00:00Z"}
}

Equals Operator ($eq)​

Implicit​

The implicit form supports strings and dates, too.

{
  "SALARY": 1000
}

Explicit​

{
  "SALARY": {"$eq": 1000}
}

Strings​

{
  "ENAME": {"$eq":"SMITH"}
}

Dates​

{
  "HIREDATE": {"$date": "1981-11-17T08:00:00Z"}
}

Not Equals Operator ($ne)​

Numbers​

{
  "SALARY": {"$ne": 1000}
}

Strings​

{
  "ENAME": {"$ne":"SMITH"}
}

Dates​

{
  "HIREDATE": {"$ne": {"$date":"1981-11-17T08:00:00Z"}}
}

Less Than Operator ($lt)​

The operator supports dates and numbers only.

Numbers​

{
  "SALARY": {"$lt": 10000}
}

Dates​

{
  "HIREDATE": {"$lt": {"$date":"1999-12-17T08:00:00Z"}}
}

Less Than or Equals Operator ($lte)​

The operator supports dates and numbers only.

Numbers​

{
  "SALARY": {"$lte": 10000}
}

Dates​

{
  "HIREDATE": {"$lte": {"$date":"1999-12-17T08:00:00Z"}}
}

Greater Than Operator ($gt)​

The operator supports dates and numbers only.

Numbers​

{
  "SALARY": {"$gt": 10000}
}

Dates​

{
  "HIREDATE": {"$gt": {"$date":"1999-12-17T08:00:00Z"}}
}

Greater Than or Equals Operator ($gte)​

The operator supports dates and numbers only.

Numbers​

{
  "SALARY": {"$gte": 10000}
}

Dates​

{
  "HIREDATE": {"$gte": {"$date":"1999-12-17T08:00:00Z"}}
}

In String Operator ($instr)​

The operator supports strings only.

{
  "ENAME": {"$instr":"MC"}
}

Not In String Operator ($ninstr)​

The operator supports strings only.

{
  "ENAME": {"$ninstr":"MC"}
}

Like Operator ($like)​

The operator supports strings. It does not support an escape character, so you cannot match a literal _ or % character.

{
  "ENAME": {"$like":"AX%"}
}

Between Operator ($between)​

The operator supports strings, dates, and numbers.

Numbers​

{
  "SALARY": {"$between": [1000,2000]}
}

Dates​

{
  "HIREDATE": {"$between": [{"$date":"1989-12-17T08:00:00Z"},{"$date":"1999-12-17T08:00:00Z"}]}
}

Strings​

{
  "ENAME": {"$between": ["A","C"]}
}

Null Ranges​

A null lower bound makes the operator equivalent to $lte, a null upper bound to $gte. Null ranges are supported by numbers and dates only.

{
  "SALARY": {"$between": [null,2000]}
}
{
  "SALARY": {"$between": [1000,null]}
}

Null Operator ($null)​

{
  "ENAME": {"$null": null}
}

Not Null Operator ($notnull)​

{
  "ENAME": {"$notnull": null}
}

And Operator ($and)​

The operator supports all operators, including $and and $or.

The following example matches a salary greater than 1000 and a name that starts with S or T:

{
  "SALARY": {"$gt": 1000},
  "ENAME": {"$or": [{"$like":"S%"}, {"$like":"T%"}]}
}

The following expression is invalid, because the operators $lt and $gt lack a column context:

{
  "$and": [{"$lt": 5000},{"$gt": 1000}]
}

The following expression is a valid alternative to the invalid one:

{
  "$and": [{"SALARY": {"$lt": 5000}}, {"SALARY": {"$gt": 1000}}]
}

Or Operator ($or)​

The operator supports all operators, including $and and $or, like the $and operator.

The following example matches a name that starts with S or a salary greater than 1000:

{
  "$or": [{"SALARY":{"$gt": 1000}},{"ENAME": {"$like":"S%"}}]
}