| Server IP : 185.208.173.17 / Your IP : 87.236.161.98 Web Server : Microsoft-IIS/10.0 System : Windows NT SRV8576125506 10.0 build 26100 (Windows Server 2016) AMD64 User : IUSR ( 0) PHP Version : 7.4.13 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : /Program Files/MySQL/MySQL Workbench 8.0/swb/shell/lib/mysqlsh/plugins/mrs_plugin/docs/ |
Upload File : |
<!DOCTYPE html>
<html lang="en" dir="ltr">
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.-->
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>MySQL REST Service - Core REST APIs</title>
<link rel="shortcut icon" href="images/favicon.png" type="image/x-icon">
<link rel="stylesheet" href="style/style.css">
<!-- cSpell:ignore pagetitle -->
<title>MySQL REST Service - Core REST APIs</title>
<style type="text/css">
code {
white-space: pre;
}
</style>
<script>
window.addEventListener('DOMContentLoaded', () => {
window.visibleItems = [];
window.lastVisible = null;
const observer = new IntersectionObserver((entries) => {
// Add active css class to first match
entries.forEach((entry) => {
const id = entry.target.getAttribute('id');
const querySel = document.querySelector(`nav li a[href="#` + id + `"]`);
if (querySel) {
const el = querySel.parentElement;
if (entry.isIntersecting) {
if (!window.visibleItems.includes(el)) {
if (window.visibleItems.length === 0 && window.lastVisible !== null) {
window.lastVisible.classList.remove('active');
window.lastVisible = el;
}
if (window.lastVisible === null) {
window.lastVisible = el;
}
window.visibleItems.push(el);
}
if (!el.classList.contains('active')) {
el.classList.add('active');
}
history.pushState({}, "", "#" + id);
} else {
if (window.visibleItems.includes(el)) {
if (window.visibleItems.length === 1) {
window.lastVisible = el;
} else {
el.classList.remove('active');
}
const index = window.visibleItems.indexOf(el);
if (index > -1) {
window.visibleItems.splice(index, 1);
}
if (window.lastVisible === el && window.visibleItems.length > 0) {
window.lastVisible === window.visibleItems[0];
}
}
}
}
});
}/*, { threshold: 0.1, rootMargin: '0px 0px -100% 0px', root: document.body }*/);
// Track all h that have an `id` applied
document.querySelectorAll('.content h1[id],h2[id]').forEach((section) => {
observer.observe(section);
});
document.querySelectorAll('a[href^="#"]').forEach(anchor => {
anchor.addEventListener('click', function (e) {
e.preventDefault();
document.querySelector(this.getAttribute('href')).scrollIntoView({
behavior: 'smooth'
});
});
});
if (typeof acquireVsCodeApi === "function") {
document.vscode = acquireVsCodeApi();
const addVsCodeDecorations = () => {
const addVsActionBtn = (e, command, caption) => {
const d = document.createElement("button");
d.innerHTML = '<i class="arrow right"></i><p>' + caption + '</p>';
d.onclick = () => {
document.vscode.postMessage({ command, path: e.innerHTML });
}
e.parentNode.insertBefore(d, e.nextSibling)
}
document.querySelectorAll("code").forEach((e) => {
if (e.innerHTML.endsWith(".sql")) {
addVsActionBtn(e, "openSqlFile", "Open in MySQL Shell");
} else if (e.innerHTML.endsWith(".mrs.json")) {
addVsActionBtn(e, "loadMrsDump", "Load MRS Dump");
} else if (e.innerHTML.startsWith("VSCodeProject:") > 0) {
e.innerHTML = e.innerHTML.slice("VSCodeProject:".length);
addVsActionBtn(e, "saveProject", "Save Project");
}
});
}
addVsCodeDecorations();
} else {
document.querySelectorAll("code").forEach((e) => {
if (e.innerHTML.startsWith("VSCodeProject:") > 0) {
e.innerHTML = e.innerHTML.slice("VSCodeProject:".length);
}
});
}
});
// Handle the message inside the webview
window.addEventListener('message', event => {
const message = event.data; // The JSON data our extension sent
switch (message.command) {
case 'goToId':
if (message.id) {
window.location = "#" + message.id;
}
break;
}
});
</script>
</head>
<body>
<div class="page">
<div class="sidebar">
<div class="bookOverview">
<h1>MySQL REST Service</h1>
<h2>
<div class="icon bookIcon"></div> <a href="index.html#document-top">Reference Manual</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="quickstart.html#document-top">Quickstart Guide</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="sql.html#document-top">SQL Reference</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="restApi.html#document-top">Core REST APIs</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="sdk.html#document-top">SDK Reference</a>
</h2>
</div>
<div class="sidebarContent">
<nav class="section-nav">
<div class="bookToc">
<br>
<h3>MySQL REST Service - Core REST
APIs 2026.3.0+9.6.1</h3>
<ul>
<li><a
href="#mrs-core-rest-apis"
id="toc-mrs-core-rest-apis"><span
class="toc-section-number">1</span>
MRS Core REST APIs</a>
<ul>
<li><a
href="#about-mrs-restful-web-services"
id="toc-about-mrs-restful-web-services"><span
class="toc-section-number">1.1</span>
About MRS RESTful Web
Services</a></li>
<li><a
href="#about-request-path-syntax-requirements"
id="toc-about-request-path-syntax-requirements"><span
class="toc-section-number">1.2</span>
About Request Path
Syntax
Requirements</a></li>
<li><a
href="#about-curl-and-testing-restful-services"
id="toc-about-curl-and-testing-restful-services"><span
class="toc-section-number">1.3</span>
About cURL and Testing
RESTful
Services</a></li>
</ul></li>
<li><a
href="#mrs-rest-queries"
id="toc-mrs-rest-queries"><span
class="toc-section-number">2</span>
MRS REST Queries</a>
<ul>
<li><a
href="#get-schema-metadata"
id="toc-get-schema-metadata"><span
class="toc-section-number">2.1</span>
Get Schema
Metadata</a></li>
<li><a
href="#get-object-metadata"
id="toc-get-object-metadata"><span
class="toc-section-number">2.2</span>
Get Object
Metadata</a></li>
<li><a
href="#get-object-data"
id="toc-get-object-data"><span
class="toc-section-number">2.3</span>
Get Object Data</a></li>
<li><a
href="#insert-table-row"
id="toc-insert-table-row"><span
class="toc-section-number">2.4</span>
Insert Table
Row</a></li>
<li><a
href="#updateinsert-table-row"
id="toc-updateinsert-table-row"><span
class="toc-section-number">2.5</span>
Update/Insert Table
Row</a></li>
<li><a
href="#delete-using-filter"
id="toc-delete-using-filter"><span
class="toc-section-number">2.6</span>
Delete Using
Filter</a></li>
</ul></li>
<li><a
href="#filtering-in-rest-queries"
id="toc-filtering-in-rest-queries"><span
class="toc-section-number">3</span>
Filtering in REST
Queries</a>
<ul>
<li><a
href="#filterobject-grammar"
id="toc-filterobject-grammar"><span
class="toc-section-number">3.1</span>
FilterObject
Grammar</a></li>
</ul></li>
<li><a
href="#filterobject-grammar-examples"
id="toc-filterobject-grammar-examples"><span
class="toc-section-number">4</span>
FilterObject Grammar
Examples</a>
<ul>
<li><a
href="#order-by-property-orderby"
id="toc-order-by-property-orderby"><span
class="toc-section-number">4.1</span>
ORDER BY property
($orderby)</a></li>
<li><a
href="#asof-property-asof"
id="toc-asof-property-asof"><span
class="toc-section-number">4.2</span>
ASOF property
($asof)</a></li>
<li><a
href="#equals-operator-eq"
id="toc-equals-operator-eq"><span
class="toc-section-number">4.3</span>
EQUALS operator
($eq)</a></li>
<li><a
href="#not-equals-operator-ne"
id="toc-not-equals-operator-ne"><span
class="toc-section-number">4.4</span>
NOT EQUALS operator
($ne)</a></li>
<li><a
href="#less-than-operator-lt"
id="toc-less-than-operator-lt"><span
class="toc-section-number">4.5</span>
LESS THAN operator
($lt)</a></li>
<li><a
href="#less-than-or-equals-operator-lte"
id="toc-less-than-or-equals-operator-lte"><span
class="toc-section-number">4.6</span>
LESS THAN OR EQUALS
operator ($lte)</a></li>
<li><a
href="#greater-than-operator-gt"
id="toc-greater-than-operator-gt"><span
class="toc-section-number">4.7</span>
GREATER THAN operator
($gt)</a></li>
<li><a
href="#greater-than-or-equals-operator-gte"
id="toc-greater-than-or-equals-operator-gte"><span
class="toc-section-number">4.8</span>
GREATER THAN OR EQUALS
operator ($gte)</a></li>
<li><a
href="#in-string-operator-instr"
id="toc-in-string-operator-instr"><span
class="toc-section-number">4.9</span>
In string operator
($instr)</a></li>
<li><a
href="#not-in-string-operator-ninstr"
id="toc-not-in-string-operator-ninstr"><span
class="toc-section-number">4.10</span>
Not in string operator
($ninstr)</a></li>
<li><a
href="#like-operator-like"
id="toc-like-operator-like"><span
class="toc-section-number">4.11</span>
LIKE operator
($like)</a></li>
<li><a
href="#between-operator-between"
id="toc-between-operator-between"><span
class="toc-section-number">4.12</span>
BETWEEN operator
($between)</a></li>
<li><a
href="#null-operator-null"
id="toc-null-operator-null"><span
class="toc-section-number">4.13</span>
NULL operator
($null)</a></li>
<li><a
href="#not-null-operator-notnull"
id="toc-not-null-operator-notnull"><span
class="toc-section-number">4.14</span>
NOT NULL operator
($notnull)</a></li>
<li><a
href="#and-operator-and"
id="toc-and-operator-and"><span
class="toc-section-number">4.15</span>
AND operator
($and)</a></li>
<li><a
href="#or-operator-or"
id="toc-or-operator-or"><span
class="toc-section-number">4.16</span>
OR operator
($or)</a></li>
</ul></li>
<li><a
href="#authenticate-a-rest-user"
id="toc-authenticate-a-rest-user"><span
class="toc-section-number">5</span>
Authenticate a REST
User</a>
<ul>
<li><a
href="#mysql-internal-authentication"
id="toc-mysql-internal-authentication"><span
class="toc-section-number">5.1</span>
MySQL Internal
Authentication</a></li>
<li><a
href="#mrs-authentication"
id="toc-mrs-authentication"><span
class="toc-section-number">5.2</span>
MRS
Authentication</a></li>
<li><a href="#oauth"
id="toc-oauth"><span
class="toc-section-number">5.3</span>
OAuth</a></li>
<li><a
href="#executing-crud-operations-on-a-rest-object"
id="toc-executing-crud-operations-on-a-rest-object"><span
class="toc-section-number">5.4</span>
Executing CRUD
Operations on a REST
Object</a></li>
</ul></li>
</ul>
</div>
</nav>
</div>
</div>
<div class="content">
<div class="header" id="document-top">
<h1>
<div class="iconBig bookIcon"></div> MySQL REST
Service - Core REST APIs
</h1>
</div>
<div class="main">
<p>This book provides examples of using the MySQL REST
Service queries and other operations against tables and
views after you have REST-enabled them.</p>
<p><strong>Chapter Overview</strong></p>
<ul>
<li><a href="#about-mrs-restful-web-services">About MRS
RESTful Web Services</a></li>
<li><a href="#get-schema-metadata">Get Schema
Metadata</a></li>
<li><a href="#get-object-metadata">Get Object
Metadata</a></li>
<li><a href="#get-object-data">Get Object Data</a></li>
<li><a href="#insert-table-row">Insert Table
Row</a></li>
<li><a href="#delete-using-filter">Delete Using
Filter</a></li>
<li><a href="#authenticate-a-rest-user">Authenticate a
REST User</a></li>
<li><a href="#filterobject-grammar">FilterObject
Grammar</a></li>
<li><a href="#filterobject-grammar-examples">Examples:
FilterObject Specifications</a></li>
</ul>
<hr />
<p>Please also see</p>
<ul>
<li><strong><a href="index.html">MySQL REST Service -
Developer’s Guide</a></strong> - This book explains how
to install and configure the MySQL REST Service (MRS) as
well as how to access the data through REST calls.</li>
<li><strong><a href="quickstart.html">MySQL REST Service
- Quickstart Guide</a></strong> - This book provides a
hands-on approach to learning how to use the MySQL REST
service.</li>
<li><strong><a href="sdk.html">MySQL REST Service - SDK
Reference</a></strong> - This book explains how to work
with the MRS Software Development Kit and discusses the
Client APIs.</li>
<li><strong><a href="sql.html">MySQL REST Service - SQL
Reference</a></strong> - This book discusses the MySQL
REST Service SQL Extension.</li>
</ul>
<hr />
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="1" id="mrs-core-rest-apis"><span
class="header-section-number">1</span> MRS Core REST
APIs</h1>
<p>This section provides examples of using the MySQL
REST Service queries and other operations against tables
and views after you have REST-enabled them.</p>
<p>The examples in this section provide an insight into
the direct, low-level REST API calls that can be made
against the MySQL REST Service. They can be used to gain
a deep understanding of how MRS works.</p>
<p>When developing an application with MRS, it is
recommended to use a higher-level MRS Software
Development Kit (SDK) instead. Please check if a <a
href="sdk.html#document-top">SDK</a> has already been
made available for your programming language and
platform.</p>
<h2 data-number="1.1"
id="about-mrs-restful-web-services"><span
class="header-section-number">1.1</span> About MRS
RESTful Web Services</h2>
<p>MRS supports the creation of an unlimited amount of
distinct RESTful Web Services. You can also refer to
them as MRS services. Each of those MRS services usually
maps to one (or more) web applications.</p>
<p>After you create a RESTful Web Service, you can
access it by navigating to the following URL.</p>
<p>Pattern:</p>
<pre><code>https://<HOSTNAME:PORT>/<MRS_SERVICE_PATH>/<MRS_DATABASE_SCHEMA_PATH>/<MRS_DATABASE_OBJECT_PATH>/</code></pre>
<ul>
<li><code>HOSTNAME:PORT/MRS_SERVICE_PATH</code>:
Specifies the address at which the given MRS service is
running. You can also refer to it as the MRS service
URI.</li>
<li><code>MRS_DATABASE_SCHEMA_PATH</code>: Specifies the
path that you provided while REST-enabling your database
schema. By default, it is the name of the schema.</li>
<li><code>MRS_DATABASE_OBJECT_PATH</code>: Specifies the
path that you provided while REST-enabling your database
object (TABLE, VIEW or PROCEDURE).</li>
</ul>
<p>Together, these values comprise the MRS endpoint
URL.</p>
<p>Example:</p>
<pre><code>https://localhost:8000/mrs/sakila/actor</code></pre>
<h2 data-number="1.2"
id="about-request-path-syntax-requirements"><span
class="header-section-number">1.2</span> About Request
Path Syntax Requirements</h2>
<p>To prevent path-based attacks, MRS requires the
syntax of the path element of each request URL to
conform to the following rules:</p>
<ul>
<li>Is not empty or whitespace-only</li>
<li>Does not contain any of the following characters: ?,
#, ;, %</li>
<li>Does not contain the null character (000)</li>
<li>Does not contain characters in the range:
001-031</li>
<li>Does not end with white space or a period (.)</li>
<li>Does not contain double forward slash (//) or double
back slash(\)</li>
<li>Does not contain two or more periods in sequence
(.., …, and so on)</li>
<li>Total length is {<span class="citation"
data-cites="value">@value</span> #MAX_PATH_LENGTH}
characters or less</li>
<li>Does not match any of the following names (case
insensitive), with or without file extensions: CON, PRN,
AUX, CLOCK$, NUL, COM0, COM1, COM2, COM3, COM4, COM5,
COM6, COM7, COM8, COM9, LPT0, LPT1, LPT2, LPT3, LPT4,
LPT5, LPT6, LPT7, LPT8, LPT9</li>
</ul>
<p>If you intend to enable REST endpoints for database
objects, then avoid object names that do not comply with
these requirements. For example, do not create a table
named #EMPS. If you do want to auto-REST enable objects
that have non-compliant names, then you must use an
alias that complies with the requirements.</p>
<p>These requirements are applied to the URL decoded
form of the URL, to prevent attempted circumvention of
percent encodings.</p>
<h2 data-number="1.3"
id="about-curl-and-testing-restful-services"><span
class="header-section-number">1.3</span> About cURL and
Testing RESTful Services</h2>
<p>Usually you can navigate to a URL of a RESTful
service using a web browser. However, another way to
test it is by using a command line tool like cURL.</p>
<p>cURL enables you to see and control what data is
being sent to and received from a RESTful service.</p>
<pre><code>curl -i https://localhost:8000/mrs/sakila/actor/2</code></pre>
<p>This example produces a response like the
following:</p>
<pre><code>{
"links": [
{
"rel": "self",
"href": "http://localhost:8000/mrs/sakila/actor/2"
}
],
"actor_id": 2,
"last_name": "WAHLBERG",
"first_name": "NICK",
"last_update": "2006-02-15 03:34:33.000000"
}</code></pre>
<p>The -i option tells cURL to display the HTTP headers
returned by the server.</p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="2" id="mrs-rest-queries"><span
class="header-section-number">2</span> MRS REST
Queries</h1>
<p>A MRS REST service provides access to one or more
schemas (and their metadata) as well as their comprising
database objects such as tables, views and procedures
(and their metadata).</p>
<h2 data-number="2.1" id="get-schema-metadata"><span
class="header-section-number">2.1</span> Get Schema
Metadata</h2>
<p>This example retrieves a list of resources available
through the specified schema alias. It shows RESTful
services that are created by enabling a table, view or
procedure.</p>
<p>Pattern:</p>
<pre><code>GET http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/metadata-catalog/</code></pre>
<p>Example:</p>
<pre><code>GET http://localhost:8000/mrs/sakila/metadata-catalog/</code></pre>
<p>Result:</p>
<pre><code>{
"items": [
{
"name": "/actor",
"links": [
{
"rel": "describes",
"href": "/mrs/sakila/actor"
},
{
"rel": "canonical",
"href": "/mrs/sakila/metadata-catalog/actor"
}
]
},
{
"name": "/address",
"links": [
{
"rel": "describes",
"href": "/mrs/sakila/address"
},
{
"rel": "canonical",
"href": "/mrs/sakila/metadata-catalog/address"
}
]
}
],
"limit": 25,
"offset": 0,
"hasMore": false,
"count": 2,
"links": [
{
"rel": "self",
"href": "/mrs/sakila/metadata-catalog/"
}
]
}</code></pre>
<p>Each available resource has two hyperlinks:</p>
<ul>
<li>The link with a “describes” relation points to the
actual resource</li>
<li>The link with a “canonical” relation points to the
resource metadata</li>
</ul>
<h2 data-number="2.2" id="get-object-metadata"><span
class="header-section-number">2.2</span> Get Object
Metadata</h2>
<p>This example retrieves the metadata (which describes
the object) of an individual object. The location of the
metadata is specified by the canonical link
relation.</p>
<p>Pattern:</p>
<pre><code>GET http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/metadata-catalog/<ObjectAlias>/</code></pre>
<p>Example:</p>
<pre><code>GET http://localhost:8000/mrs/sakila/metadata-catalog/actor/</code></pre>
<p>Result:</p>
<pre><code>{
"name": "/actor",
"primaryKey": [
"actor_id"
],
"members": [
{
"name": "actor_id",
"type": "null"
},
{
"name": "first_name",
"type": "null"
},
{
"name": "last_name",
"type": "null"
},
{
"name": "last_update",
"type": "string"
}
],
"links": [
{
"rel": "collection",
"href": "/mrs/sakila/metadata-catalog",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "/mrs/sakila/metadata-catalog/actor"
},
{
"rel": "describes",
"href": "/mrs/sakila/actor"
}
]
}</code></pre>
<h2 data-number="2.3" id="get-object-data"><span
class="header-section-number">2.3</span> Get Object
Data</h2>
<p>This example retrieves the data in the object. Each
row in the object corresponds to a JSON object embedded
within the JSON array</p>
<p>Pattern:</p>
<pre><code>GET http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/<ObjectAlias>/</code></pre>
<p>Example:</p>
<pre><code>GET http://localhost:8000/mrs/sakila/actor/</code></pre>
<p>Result:</p>
<pre><code>{
"items": [
{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/1"
}
],
"actor_id": 1,
"last_name": "GUINESSS",
"first_name": "PENELOPE",
"last_update": "2021-09-28 20:18:53.000000"
},
{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/2"
}
],
"actor_id": 2,
"last_name": "WAHLBERG",
"first_name": "NICK",
"last_update": "2006-02-15 03:34:33.000000"
},
{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/3"
}
],
"actor_id": 3,
"last_name": "CHASE",
"first_name": "ED",
"last_update": "2006-02-15 03:34:33.000000"
},
...
]
}</code></pre>
<h3 data-number="2.3.1"
id="get-table-data-using-pagination"><span
class="header-section-number">2.3.1</span> Get Table
Data Using Pagination</h3>
<p>We can specify offset and limit parameters which are
used for result data pagination.</p>
<p>Pattern:</p>
<pre><code>GET http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/<ObjectAlias>/?offset=<Offset>&limit=<Limit></code></pre>
<p>Example:</p>
<pre><code>GET http://localhost:8080/mrs/sakila/actor/?offset=10&limit=2</code></pre>
<p>Result:</p>
<pre><code>{
"items": [
{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/11"
}
],
"actor_id": 11,
"last_name": "CAGE",
"first_name": "ZERO",
"last_update": "2006-02-15 03:34:33.000000"
},
{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/12"
}
],
"actor_id": 12,
"last_name": "BERRY",
"first_name": "KARL",
"last_update": "2006-02-15 03:34:33.000000"
}
],
"limit": 2,
"offset": 10,
"hasMore": true,
"count": 2,
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/"
},
{
"rel": "next",
"href": "/mrs/sakila/actor/?offset=12&limit=2"
},
{
"rel": "prev",
"href": "/mrs/sakila/actor/?offset=8&limit=2"
},
{
"rel": "first",
"href": "/mrs/sakila/actor/?limit=2"
}
]
}</code></pre>
<h3 data-number="2.3.2"
id="get-table-data-using-query"><span
class="header-section-number">2.3.2</span> Get Table
Data Using Query</h3>
<p>We can use a filter clause to restrict the set of
objects that are returned.</p>
<p>Pattern:</p>
<pre><code>GET http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/<ObjectAlias>/?q=<FilterClause></code></pre>
<p>Example:</p>
<pre><code>GET http://localhost:8080/mrs/sakila/actor/?q={"last_name":{"$like":"WAW%"}}</code></pre>
<p>Result:</p>
<pre><code>{
"items": [
{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/97"
}
],
"actor_id": 97,
"last_name": "HAWKE",
"first_name": "MEG",
"last_update": "2006-02-15 03:34:33.000000"
}
],
"limit": 25,
"offset": 0,
"hasMore": false,
"count": 1,
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/"
}
]
}</code></pre>
<h3 data-number="2.3.3"
id="get-table-row-using-primary-key"><span
class="header-section-number">2.3.3</span> Get Table Row
Using Primary Key</h3>
<p>This example retrieves an object by specifying its
identifying key values.</p>
<p>Note: A table requires a primary key to be part of a
REST service.</p>
<p>Pattern:</p>
<pre><code>GET http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/<ObjectAlias>/<KeyValues></code></pre>
<p>Where <code><KeyValues></code> is a
comma-separated list of key values (in key order).</p>
<p>Example:</p>
<pre><code>GET http://localhost:8000/mrs/sakila/actor/53</code></pre>
<p>Result:</p>
<pre><code>{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/53"
}
],
"actor_id": 53,
"last_name": "TEMPLE",
"first_name": "MENA",
"last_update": "2006-02-15 03:34:33.000000"
}</code></pre>
<h2 data-number="2.4" id="insert-table-row"><span
class="header-section-number">2.4</span> Insert Table
Row</h2>
<p>To insert data into a table, the request body should
be a JSON object that contains the data to be
inserted.</p>
<p>If the object has a primary key, then the POST
request can include the primary key value in the body.
If the table has an AUTO_INCREMENT column then the
primary key column may be omitted.</p>
<p>Pattern:</p>
<pre><code>POST http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/<ObjectAlias>/</code></pre>
<p>Example:</p>
<pre><code>curl -i -H "Content-Type: application/json" -X POST -d "{ \"last_name\" : \"FOLEY\", \"first_name\": \"MIKE\" }" "http://localhost:8000/mrs/sakila/actor/" Content-Type: application/json</code></pre>
<p>Result:</p>
<pre><code>{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/201"
}
],
"actor_id": 201,
"last_name": "FOLEY",
"first_name": "MIKE",
"last_update": "2022-11-29 15:35:17.000000"
}</code></pre>
<h2 data-number="2.5" id="updateinsert-table-row"><span
class="header-section-number">2.5</span> Update/Insert
Table Row</h2>
<p>To insert, update or “upsert” (update if exists,
insert if not) data into a table, we can send a request
where the body contains a JSON object with the data to
insert or update.</p>
<p>Pattern:</p>
<pre><code>PUT http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/<ObjectAlias>/<KeyValues></code></pre>
<p>Example:</p>
<pre><code>curl -i -H "Content-Type: application/json" -X PUT -d "{ \"last_name\" : \"FOLEY\", \"first_name\": \"JACK\" }" "https://localhost:8000/mrs/sakila/actor/201" Content-Type: application/json</code></pre>
<p>Result:</p>
<pre><code>{
"links": [
{
"rel": "self",
"href": "/mrs/sakila/actor/201"
}
],
"actor_id": 201,
"last_name": "FOLEY",
"first_name": "JACK",
"last_update": "2022-11-29 15:45:10.000000"
}</code></pre>
<h2 data-number="2.6" id="delete-using-filter"><span
class="header-section-number">2.6</span> Delete Using
Filter</h2>
<p>Deleting a object or other database object can be
done by specifying a filter clause that identifies the
object to delete.</p>
<p>Pattern:</p>
<pre><code>DELETE http://<HOST>:<PORT>/<ServiceAlias>/<SchemaAlias>/<ObjectAlias>/?q=<FilterClause></code></pre>
<p>Example:</p>
<pre><code>curl -i -X DELETE "https://localhost:8000/mrs/sakila/actor/?q=\{\"actor_id\":201\}"</code></pre>
<p>Result:</p>
<pre><code>{
"itemsDeleted": 1
}</code></pre>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="3" id="filtering-in-rest-queries"><span
class="header-section-number">3</span> Filtering in REST
Queries</h1>
<p>This section details the process of filtering in
queries against REST-enabled tables and views. The next
section offers practical examples to illustrate the
concepts.</p>
<p>Filtering involves the process of limiting a
collection resource by using a per-request dynamic
filter definition across multiple page resources. Each
page contains a subset of items found in the complete
collection. Filtering enables efficient traversal of
large collections.</p>
<p>To implement filtering in a query, incorporate the
parameter q=FilterObject, where FilterObject is a JSON
object specifying the custom selection and sorting to be
applied to the resource. To illustrate, consider the
following example:</p>
<div class="sourceCode" id="cb32"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb32-1"><a href="#cb32-1" aria-hidden="true" tabindex="-1"></a>https://example.com/myService/sakila/actor/</span></code></pre></div>
<p>The following query contains a filter that restricts
the <code>first_name</code> column to “BRUCE”. Note that
the REST object was created using the default JSON field
mapping, which translates the database column
<code>first_name</code> (snake_case) to the JSON field
<code>firstName</code> (camelCase).</p>
<div class="sourceCode" id="cb33"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb33-1"><a href="#cb33-1" aria-hidden="true" tabindex="-1"></a>https://example.com/myService/sakila/actor/?q={"firstName":"BRUCE"}</span></code></pre></div>
<h2 data-number="3.1" id="filterobject-grammar"><span
class="header-section-number">3.1</span> FilterObject
Grammar</h2>
<p>The FilterObject must be a JSON object that complies
with the following syntax:</p>
<div class="sourceCode" id="cb34"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb34-1"><a href="#cb34-1" aria-hidden="true" tabindex="-1"></a> FilterObject { orderby , asof, wmembers }</span></code></pre></div>
<p>The orderby, asof, and wmembers attributes are
optional, and their definitions are as follows:</p>
<div class="sourceCode" id="cb35"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb35-1"><a href="#cb35-1" aria-hidden="true" tabindex="-1"></a>orderby</span>
<span id="cb35-2"><a href="#cb35-2" aria-hidden="true" tabindex="-1"></a> "$orderby": {orderByMembers}</span>
<span id="cb35-3"><a href="#cb35-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-4"><a href="#cb35-4" aria-hidden="true" tabindex="-1"></a>orderByMembers</span>
<span id="cb35-5"><a href="#cb35-5" aria-hidden="true" tabindex="-1"></a> orderByProperty</span>
<span id="cb35-6"><a href="#cb35-6" aria-hidden="true" tabindex="-1"></a> orderByProperty , orderByMembers</span>
<span id="cb35-7"><a href="#cb35-7" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-8"><a href="#cb35-8" aria-hidden="true" tabindex="-1"></a>orderByProperty</span>
<span id="cb35-9"><a href="#cb35-9" aria-hidden="true" tabindex="-1"></a> columnName : sortingValue</span>
<span id="cb35-10"><a href="#cb35-10" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-11"><a href="#cb35-11" aria-hidden="true" tabindex="-1"></a>sortingValue</span>
<span id="cb35-12"><a href="#cb35-12" aria-hidden="true" tabindex="-1"></a> "ASC"</span>
<span id="cb35-13"><a href="#cb35-13" aria-hidden="true" tabindex="-1"></a> "DESC"</span>
<span id="cb35-14"><a href="#cb35-14" aria-hidden="true" tabindex="-1"></a> "-1"</span>
<span id="cb35-15"><a href="#cb35-15" aria-hidden="true" tabindex="-1"></a> "1"</span>
<span id="cb35-16"><a href="#cb35-16" aria-hidden="true" tabindex="-1"></a> -1</span>
<span id="cb35-17"><a href="#cb35-17" aria-hidden="true" tabindex="-1"></a> 1</span>
<span id="cb35-18"><a href="#cb35-18" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-19"><a href="#cb35-19" aria-hidden="true" tabindex="-1"></a>asof</span>
<span id="cb35-20"><a href="#cb35-20" aria-hidden="true" tabindex="-1"></a> "$asof": gtid</span>
<span id="cb35-21"><a href="#cb35-21" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-22"><a href="#cb35-22" aria-hidden="true" tabindex="-1"></a>wmembers</span>
<span id="cb35-23"><a href="#cb35-23" aria-hidden="true" tabindex="-1"></a> wpair</span>
<span id="cb35-24"><a href="#cb35-24" aria-hidden="true" tabindex="-1"></a> wpair , wmembers</span>
<span id="cb35-25"><a href="#cb35-25" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-26"><a href="#cb35-26" aria-hidden="true" tabindex="-1"></a>wpair</span>
<span id="cb35-27"><a href="#cb35-27" aria-hidden="true" tabindex="-1"></a> columnProperty</span>
<span id="cb35-28"><a href="#cb35-28" aria-hidden="true" tabindex="-1"></a> complexOperatorProperty</span>
<span id="cb35-29"><a href="#cb35-29" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-30"><a href="#cb35-30" aria-hidden="true" tabindex="-1"></a>columnProperty</span>
<span id="cb35-31"><a href="#cb35-31" aria-hidden="true" tabindex="-1"></a> columnName : string</span>
<span id="cb35-32"><a href="#cb35-32" aria-hidden="true" tabindex="-1"></a> columnName : number</span>
<span id="cb35-33"><a href="#cb35-33" aria-hidden="true" tabindex="-1"></a> columnName : date</span>
<span id="cb35-34"><a href="#cb35-34" aria-hidden="true" tabindex="-1"></a> columnName : geo</span>
<span id="cb35-35"><a href="#cb35-35" aria-hidden="true" tabindex="-1"></a> columnName : vector</span>
<span id="cb35-36"><a href="#cb35-36" aria-hidden="true" tabindex="-1"></a> columnName : boolean</span>
<span id="cb35-37"><a href="#cb35-37" aria-hidden="true" tabindex="-1"></a> columnName : simpleOperatorObject</span>
<span id="cb35-38"><a href="#cb35-38" aria-hidden="true" tabindex="-1"></a> columnName : complexOperatorObject</span>
<span id="cb35-39"><a href="#cb35-39" aria-hidden="true" tabindex="-1"></a> columnName : [complexValues]</span>
<span id="cb35-40"><a href="#cb35-40" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-41"><a href="#cb35-41" aria-hidden="true" tabindex="-1"></a>columnName</span>
<span id="cb35-42"><a href="#cb35-42" aria-hidden="true" tabindex="-1"></a>"\p{Alpha}[[\p{Alpha}]]([[\p{Alnum}]#$_])*$"</span>
<span id="cb35-43"><a href="#cb35-43" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-44"><a href="#cb35-44" aria-hidden="true" tabindex="-1"></a>complexOperatorProperty</span>
<span id="cb35-45"><a href="#cb35-45" aria-hidden="true" tabindex="-1"></a> complexKey : [complexValues]</span>
<span id="cb35-46"><a href="#cb35-46" aria-hidden="true" tabindex="-1"></a> complexKey : simpleOperatorObject</span>
<span id="cb35-47"><a href="#cb35-47" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-48"><a href="#cb35-48" aria-hidden="true" tabindex="-1"></a>complexKey</span>
<span id="cb35-49"><a href="#cb35-49" aria-hidden="true" tabindex="-1"></a> "$and"</span>
<span id="cb35-50"><a href="#cb35-50" aria-hidden="true" tabindex="-1"></a> "$or"</span>
<span id="cb35-51"><a href="#cb35-51" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-52"><a href="#cb35-52" aria-hidden="true" tabindex="-1"></a>complexValues</span>
<span id="cb35-53"><a href="#cb35-53" aria-hidden="true" tabindex="-1"></a> complexValue , complexValues</span>
<span id="cb35-54"><a href="#cb35-54" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-55"><a href="#cb35-55" aria-hidden="true" tabindex="-1"></a>complexValue</span>
<span id="cb35-56"><a href="#cb35-56" aria-hidden="true" tabindex="-1"></a> simpleOperatorObject</span>
<span id="cb35-57"><a href="#cb35-57" aria-hidden="true" tabindex="-1"></a> complexOperatorObject</span>
<span id="cb35-58"><a href="#cb35-58" aria-hidden="true" tabindex="-1"></a> columnObject</span>
<span id="cb35-59"><a href="#cb35-59" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-60"><a href="#cb35-60" aria-hidden="true" tabindex="-1"></a>columnObject</span>
<span id="cb35-61"><a href="#cb35-61" aria-hidden="true" tabindex="-1"></a> {columnProperty}</span>
<span id="cb35-62"><a href="#cb35-62" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-63"><a href="#cb35-63" aria-hidden="true" tabindex="-1"></a>simpleOperatorObject</span>
<span id="cb35-64"><a href="#cb35-64" aria-hidden="true" tabindex="-1"></a> {simpleOperatorProperty}</span>
<span id="cb35-65"><a href="#cb35-65" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-66"><a href="#cb35-66" aria-hidden="true" tabindex="-1"></a>complexOperatorObject</span>
<span id="cb35-67"><a href="#cb35-67" aria-hidden="true" tabindex="-1"></a> {complexOperatorProperty}</span>
<span id="cb35-68"><a href="#cb35-68" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-69"><a href="#cb35-69" aria-hidden="true" tabindex="-1"></a>simpleOperatorProperty</span>
<span id="cb35-70"><a href="#cb35-70" aria-hidden="true" tabindex="-1"></a> "$eq" : string | number | date | geo | vector | boolean</span>
<span id="cb35-71"><a href="#cb35-71" aria-hidden="true" tabindex="-1"></a> "$ne" : string | number | date | geo | vector | boolean</span>
<span id="cb35-72"><a href="#cb35-72" aria-hidden="true" tabindex="-1"></a> "$lt" : number | date</span>
<span id="cb35-73"><a href="#cb35-73" aria-hidden="true" tabindex="-1"></a> "$lte" : number | date</span>
<span id="cb35-74"><a href="#cb35-74" aria-hidden="true" tabindex="-1"></a> "$gt" : number | date</span>
<span id="cb35-75"><a href="#cb35-75" aria-hidden="true" tabindex="-1"></a> "$gte" : number | date</span>
<span id="cb35-76"><a href="#cb35-76" aria-hidden="true" tabindex="-1"></a> "$instr" : string</span>
<span id="cb35-77"><a href="#cb35-77" aria-hidden="true" tabindex="-1"></a> "$ninstr" : string</span>
<span id="cb35-78"><a href="#cb35-78" aria-hidden="true" tabindex="-1"></a> "$like" : string</span>
<span id="cb35-79"><a href="#cb35-79" aria-hidden="true" tabindex="-1"></a> "$null" : null</span>
<span id="cb35-80"><a href="#cb35-80" aria-hidden="true" tabindex="-1"></a> "$notnull" : null</span>
<span id="cb35-81"><a href="#cb35-81" aria-hidden="true" tabindex="-1"></a> "$between" : betweenValue</span>
<span id="cb35-82"><a href="#cb35-82" aria-hidden="true" tabindex="-1"></a> "$match": fullTextSearch</span>
<span id="cb35-83"><a href="#cb35-83" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-84"><a href="#cb35-84" aria-hidden="true" tabindex="-1"></a>betweenValue</span>
<span id="cb35-85"><a href="#cb35-85" aria-hidden="true" tabindex="-1"></a> [null , betweenNotNull]</span>
<span id="cb35-86"><a href="#cb35-86" aria-hidden="true" tabindex="-1"></a> [betweenNotNull , null]</span>
<span id="cb35-87"><a href="#cb35-87" aria-hidden="true" tabindex="-1"></a> [betweenRegular , betweenRegular]</span>
<span id="cb35-88"><a href="#cb35-88" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-89"><a href="#cb35-89" aria-hidden="true" tabindex="-1"></a>betweenNotNull</span>
<span id="cb35-90"><a href="#cb35-90" aria-hidden="true" tabindex="-1"></a> number</span>
<span id="cb35-91"><a href="#cb35-91" aria-hidden="true" tabindex="-1"></a> date</span>
<span id="cb35-92"><a href="#cb35-92" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-93"><a href="#cb35-93" aria-hidden="true" tabindex="-1"></a>betweenRegular</span>
<span id="cb35-94"><a href="#cb35-94" aria-hidden="true" tabindex="-1"></a> string</span>
<span id="cb35-95"><a href="#cb35-95" aria-hidden="true" tabindex="-1"></a> number</span>
<span id="cb35-96"><a href="#cb35-96" aria-hidden="true" tabindex="-1"></a> date</span>
<span id="cb35-97"><a href="#cb35-97" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb35-98"><a href="#cb35-98" aria-hidden="true" tabindex="-1"></a>fullTextSearch</span>
<span id="cb35-99"><a href="#cb35-99" aria-hidden="true" tabindex="-1"></a> {"$params":[fieldList], "$against":{"$expr":fullTextExpr}}</span>
<span id="cb35-100"><a href="#cb35-100" aria-hidden="true" tabindex="-1"></a> {"$params":[fieldList], "$against":{"$expr":fullTextExpr, "$modifier":fullTextMod}}</span></code></pre></div>
<p>Data type definitions include the following:</p>
<div class="sourceCode" id="cb36"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb36-1"><a href="#cb36-1" aria-hidden="true" tabindex="-1"></a>string</span>
<span id="cb36-2"><a href="#cb36-2" aria-hidden="true" tabindex="-1"></a> JSONString</span>
<span id="cb36-3"><a href="#cb36-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-4"><a href="#cb36-4" aria-hidden="true" tabindex="-1"></a>number</span>
<span id="cb36-5"><a href="#cb36-5" aria-hidden="true" tabindex="-1"></a> JSONNumber</span>
<span id="cb36-6"><a href="#cb36-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-7"><a href="#cb36-7" aria-hidden="true" tabindex="-1"></a>date</span>
<span id="cb36-8"><a href="#cb36-8" aria-hidden="true" tabindex="-1"></a> {"$date":"datechars"}</span>
<span id="cb36-9"><a href="#cb36-9" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-10"><a href="#cb36-10" aria-hidden="true" tabindex="-1"></a>gtid</span>
<span id="cb36-11"><a href="#cb36-11" aria-hidden="true" tabindex="-1"></a> JSONString</span>
<span id="cb36-12"><a href="#cb36-12" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-13"><a href="#cb36-13" aria-hidden="true" tabindex="-1"></a>geo</span>
<span id="cb36-14"><a href="#cb36-14" aria-hidden="true" tabindex="-1"></a> https://en.wikipedia.org/wiki/GeoJSON</span>
<span id="cb36-15"><a href="#cb36-15" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-16"><a href="#cb36-16" aria-hidden="true" tabindex="-1"></a>vector</span>
<span id="cb36-17"><a href="#cb36-17" aria-hidden="true" tabindex="-1"></a> [numberList]</span>
<span id="cb36-18"><a href="#cb36-18" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-19"><a href="#cb36-19" aria-hidden="true" tabindex="-1"></a>numberList</span>
<span id="cb36-20"><a href="#cb36-20" aria-hidden="true" tabindex="-1"></a> number, numberList</span>
<span id="cb36-21"><a href="#cb36-21" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-22"><a href="#cb36-22" aria-hidden="true" tabindex="-1"></a>fieldList</span>
<span id="cb36-23"><a href="#cb36-23" aria-hidden="true" tabindex="-1"></a> fieldName, fieldList</span>
<span id="cb36-24"><a href="#cb36-24" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-25"><a href="#cb36-25" aria-hidden="true" tabindex="-1"></a>fieldName: JSONString</span>
<span id="cb36-26"><a href="#cb36-26" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-27"><a href="#cb36-27" aria-hidden="true" tabindex="-1"></a>fullTextExpr: JSONString</span>
<span id="cb36-28"><a href="#cb36-28" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb36-29"><a href="#cb36-29" aria-hidden="true" tabindex="-1"></a>fullTextMod:</span>
<span id="cb36-30"><a href="#cb36-30" aria-hidden="true" tabindex="-1"></a> "IN NATURAL LANGUAGE MODE"</span>
<span id="cb36-31"><a href="#cb36-31" aria-hidden="true" tabindex="-1"></a> "IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION"</span>
<span id="cb36-32"><a href="#cb36-32" aria-hidden="true" tabindex="-1"></a> "IN BOOLEAN MODE"</span>
<span id="cb36-33"><a href="#cb36-33" aria-hidden="true" tabindex="-1"></a> "WITH QUERY EXPANSION"</span></code></pre></div>
<p>Where:</p>
<div class="sourceCode" id="cb37"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb37-1"><a href="#cb37-1" aria-hidden="true" tabindex="-1"></a>datechars is an RFC3339 date format in UTC (Z)</span>
<span id="cb37-2"><a href="#cb37-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb37-3"><a href="#cb37-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb37-4"><a href="#cb37-4" aria-hidden="true" tabindex="-1"></a>JSONString</span>
<span id="cb37-5"><a href="#cb37-5" aria-hidden="true" tabindex="-1"></a> ""</span>
<span id="cb37-6"><a href="#cb37-6" aria-hidden="true" tabindex="-1"></a> " chars "</span>
<span id="cb37-7"><a href="#cb37-7" aria-hidden="true" tabindex="-1"></a>chars</span>
<span id="cb37-8"><a href="#cb37-8" aria-hidden="true" tabindex="-1"></a> char</span>
<span id="cb37-9"><a href="#cb37-9" aria-hidden="true" tabindex="-1"></a> char chars</span>
<span id="cb37-10"><a href="#cb37-10" aria-hidden="true" tabindex="-1"></a>char</span>
<span id="cb37-11"><a href="#cb37-11" aria-hidden="true" tabindex="-1"></a> any-Unicode-character except-"-or-\-or-control-character</span>
<span id="cb37-12"><a href="#cb37-12" aria-hidden="true" tabindex="-1"></a> \"</span>
<span id="cb37-13"><a href="#cb37-13" aria-hidden="true" tabindex="-1"></a> \\</span>
<span id="cb37-14"><a href="#cb37-14" aria-hidden="true" tabindex="-1"></a> \/</span>
<span id="cb37-15"><a href="#cb37-15" aria-hidden="true" tabindex="-1"></a> \b</span>
<span id="cb37-16"><a href="#cb37-16" aria-hidden="true" tabindex="-1"></a> \f</span>
<span id="cb37-17"><a href="#cb37-17" aria-hidden="true" tabindex="-1"></a> \n</span>
<span id="cb37-18"><a href="#cb37-18" aria-hidden="true" tabindex="-1"></a> \r</span>
<span id="cb37-19"><a href="#cb37-19" aria-hidden="true" tabindex="-1"></a> \t</span>
<span id="cb37-20"><a href="#cb37-20" aria-hidden="true" tabindex="-1"></a> \u four-hex-digits</span>
<span id="cb37-21"><a href="#cb37-21" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb37-22"><a href="#cb37-22" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb37-23"><a href="#cb37-23" aria-hidden="true" tabindex="-1"></a>JSONNumber</span>
<span id="cb37-24"><a href="#cb37-24" aria-hidden="true" tabindex="-1"></a> int</span>
<span id="cb37-25"><a href="#cb37-25" aria-hidden="true" tabindex="-1"></a> int frac</span>
<span id="cb37-26"><a href="#cb37-26" aria-hidden="true" tabindex="-1"></a> int exp</span>
<span id="cb37-27"><a href="#cb37-27" aria-hidden="true" tabindex="-1"></a> int frac exp</span>
<span id="cb37-28"><a href="#cb37-28" aria-hidden="true" tabindex="-1"></a>int</span>
<span id="cb37-29"><a href="#cb37-29" aria-hidden="true" tabindex="-1"></a> digit</span>
<span id="cb37-30"><a href="#cb37-30" aria-hidden="true" tabindex="-1"></a> digit1-9 digits</span>
<span id="cb37-31"><a href="#cb37-31" aria-hidden="true" tabindex="-1"></a> - digit</span>
<span id="cb37-32"><a href="#cb37-32" aria-hidden="true" tabindex="-1"></a> - digit1-9 digits</span>
<span id="cb37-33"><a href="#cb37-33" aria-hidden="true" tabindex="-1"></a>frac</span>
<span id="cb37-34"><a href="#cb37-34" aria-hidden="true" tabindex="-1"></a> . digits</span>
<span id="cb37-35"><a href="#cb37-35" aria-hidden="true" tabindex="-1"></a>exp</span>
<span id="cb37-36"><a href="#cb37-36" aria-hidden="true" tabindex="-1"></a> e digits</span>
<span id="cb37-37"><a href="#cb37-37" aria-hidden="true" tabindex="-1"></a>digits</span>
<span id="cb37-38"><a href="#cb37-38" aria-hidden="true" tabindex="-1"></a> digit</span>
<span id="cb37-39"><a href="#cb37-39" aria-hidden="true" tabindex="-1"></a> digit digits</span>
<span id="cb37-40"><a href="#cb37-40" aria-hidden="true" tabindex="-1"></a>e</span>
<span id="cb37-41"><a href="#cb37-41" aria-hidden="true" tabindex="-1"></a> e</span>
<span id="cb37-42"><a href="#cb37-42" aria-hidden="true" tabindex="-1"></a> e+</span>
<span id="cb37-43"><a href="#cb37-43" aria-hidden="true" tabindex="-1"></a> e-</span>
<span id="cb37-44"><a href="#cb37-44" aria-hidden="true" tabindex="-1"></a> E</span>
<span id="cb37-45"><a href="#cb37-45" aria-hidden="true" tabindex="-1"></a> E+</span>
<span id="cb37-46"><a href="#cb37-46" aria-hidden="true" tabindex="-1"></a> E-</span></code></pre></div>
<p>The FilterObject must be encoded according to Section
2.1 of RFC3986.</p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="4"
id="filterobject-grammar-examples"><span
class="header-section-number">4</span> FilterObject
Grammar Examples</h1>
<h2 data-number="4.1"
id="order-by-property-orderby"><span
class="header-section-number">4.1</span> ORDER BY
property ($orderby)</h2>
<div class="sourceCode" id="cb38"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb38-1"><a href="#cb38-1" aria-hidden="true" tabindex="-1"></a>Order by with literals</span>
<span id="cb38-2"><a href="#cb38-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb38-3"><a href="#cb38-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb38-4"><a href="#cb38-4" aria-hidden="true" tabindex="-1"></a> "$orderby": {"SALARY": "ASC","ENAME":"DESC"}</span>
<span id="cb38-5"><a href="#cb38-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb38-6"><a href="#cb38-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb38-7"><a href="#cb38-7" aria-hidden="true" tabindex="-1"></a>Order by with numbers</span>
<span id="cb38-8"><a href="#cb38-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb38-9"><a href="#cb38-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb38-10"><a href="#cb38-10" aria-hidden="true" tabindex="-1"></a> "$orderby": {"SALARY": -1,"ENAME": 1}</span>
<span id="cb38-11"><a href="#cb38-11" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.2" id="asof-property-asof"><span
class="header-section-number">4.2</span> ASOF property
($asof)</h2>
<div class="sourceCode" id="cb39"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb39-1"><a href="#cb39-1" aria-hidden="true" tabindex="-1"></a>With SCN (Implicit)</span>
<span id="cb39-2"><a href="#cb39-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb39-3"><a href="#cb39-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb39-4"><a href="#cb39-4" aria-hidden="true" tabindex="-1"></a> "$asof": 1273919</span>
<span id="cb39-5"><a href="#cb39-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb39-6"><a href="#cb39-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb39-7"><a href="#cb39-7" aria-hidden="true" tabindex="-1"></a>With SCN (Explicit)</span>
<span id="cb39-8"><a href="#cb39-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb39-9"><a href="#cb39-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb39-10"><a href="#cb39-10" aria-hidden="true" tabindex="-1"></a> "$asof": {"$scn": "1273919"}</span>
<span id="cb39-11"><a href="#cb39-11" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb39-12"><a href="#cb39-12" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb39-13"><a href="#cb39-13" aria-hidden="true" tabindex="-1"></a>With Date (Implicit)</span>
<span id="cb39-14"><a href="#cb39-14" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb39-15"><a href="#cb39-15" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb39-16"><a href="#cb39-16" aria-hidden="true" tabindex="-1"></a> "$asof": "2014-06-30T00:00:00Z"</span>
<span id="cb39-17"><a href="#cb39-17" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb39-18"><a href="#cb39-18" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb39-19"><a href="#cb39-19" aria-hidden="true" tabindex="-1"></a>With Date (Explicit)</span>
<span id="cb39-20"><a href="#cb39-20" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb39-21"><a href="#cb39-21" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb39-22"><a href="#cb39-22" aria-hidden="true" tabindex="-1"></a> "$asof": {"$date": "2014-06-30T00:00:00Z"}</span>
<span id="cb39-23"><a href="#cb39-23" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.3" id="equals-operator-eq"><span
class="header-section-number">4.3</span> EQUALS operator
($eq)</h2>
<div class="sourceCode" id="cb40"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb40-1"><a href="#cb40-1" aria-hidden="true" tabindex="-1"></a>Implicit (Support String and Dates too)</span>
<span id="cb40-2"><a href="#cb40-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb40-3"><a href="#cb40-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb40-4"><a href="#cb40-4" aria-hidden="true" tabindex="-1"></a> "SALARY": 1000</span>
<span id="cb40-5"><a href="#cb40-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb40-6"><a href="#cb40-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb40-7"><a href="#cb40-7" aria-hidden="true" tabindex="-1"></a>Explicit</span>
<span id="cb40-8"><a href="#cb40-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb40-9"><a href="#cb40-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb40-10"><a href="#cb40-10" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$eq": 1000}</span>
<span id="cb40-11"><a href="#cb40-11" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb40-12"><a href="#cb40-12" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb40-13"><a href="#cb40-13" aria-hidden="true" tabindex="-1"></a>Strings</span>
<span id="cb40-14"><a href="#cb40-14" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb40-15"><a href="#cb40-15" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb40-16"><a href="#cb40-16" aria-hidden="true" tabindex="-1"></a> "ENAME": {"$eq":"SMITH"}</span>
<span id="cb40-17"><a href="#cb40-17" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb40-18"><a href="#cb40-18" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb40-19"><a href="#cb40-19" aria-hidden="true" tabindex="-1"></a>Dates</span>
<span id="cb40-20"><a href="#cb40-20" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb40-21"><a href="#cb40-21" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb40-22"><a href="#cb40-22" aria-hidden="true" tabindex="-1"></a> "HIREDATE": {"$date": "1981-11-17T08:00:00Z"}</span>
<span id="cb40-23"><a href="#cb40-23" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.4" id="not-equals-operator-ne"><span
class="header-section-number">4.4</span> NOT EQUALS
operator ($ne)</h2>
<div class="sourceCode" id="cb41"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb41-1"><a href="#cb41-1" aria-hidden="true" tabindex="-1"></a>Number</span>
<span id="cb41-2"><a href="#cb41-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-3"><a href="#cb41-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb41-4"><a href="#cb41-4" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$ne": 1000}</span>
<span id="cb41-5"><a href="#cb41-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb41-6"><a href="#cb41-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-7"><a href="#cb41-7" aria-hidden="true" tabindex="-1"></a>String</span>
<span id="cb41-8"><a href="#cb41-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-9"><a href="#cb41-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb41-10"><a href="#cb41-10" aria-hidden="true" tabindex="-1"></a> "ENAME": {"$ne":"SMITH"}</span>
<span id="cb41-11"><a href="#cb41-11" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb41-12"><a href="#cb41-12" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-13"><a href="#cb41-13" aria-hidden="true" tabindex="-1"></a>Dates</span>
<span id="cb41-14"><a href="#cb41-14" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-15"><a href="#cb41-15" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb41-16"><a href="#cb41-16" aria-hidden="true" tabindex="-1"></a> "HIREDATE": {"$ne": {"$date":"1981-11-17T08:00:00Z"}}</span>
<span id="cb41-17"><a href="#cb41-17" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.5" id="less-than-operator-lt"><span
class="header-section-number">4.5</span> LESS THAN
operator ($lt)</h2>
<p>(Supports dates and numbers only)</p>
<div class="sourceCode" id="cb42"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb42-1"><a href="#cb42-1" aria-hidden="true" tabindex="-1"></a>Numbers</span>
<span id="cb42-2"><a href="#cb42-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb42-3"><a href="#cb42-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb42-4"><a href="#cb42-4" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$lt": 10000}</span>
<span id="cb42-5"><a href="#cb42-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb42-6"><a href="#cb42-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb42-7"><a href="#cb42-7" aria-hidden="true" tabindex="-1"></a>Dates</span>
<span id="cb42-8"><a href="#cb42-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb42-9"><a href="#cb42-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb42-10"><a href="#cb42-10" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$lt": {"$date":"1999-12-17T08:00:00Z"}}</span>
<span id="cb42-11"><a href="#cb42-11" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.6"
id="less-than-or-equals-operator-lte"><span
class="header-section-number">4.6</span> LESS THAN OR
EQUALS operator ($lte)</h2>
<p>(Supports dates and numbers only)</p>
<div class="sourceCode" id="cb43"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb43-1"><a href="#cb43-1" aria-hidden="true" tabindex="-1"></a>Numbers</span>
<span id="cb43-2"><a href="#cb43-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb43-3"><a href="#cb43-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb43-4"><a href="#cb43-4" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$lte": 10000}</span>
<span id="cb43-5"><a href="#cb43-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb43-6"><a href="#cb43-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb43-7"><a href="#cb43-7" aria-hidden="true" tabindex="-1"></a>Dates</span>
<span id="cb43-8"><a href="#cb43-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb43-9"><a href="#cb43-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb43-10"><a href="#cb43-10" aria-hidden="true" tabindex="-1"></a> "HIREDATE": {"$lte": {"$date":"1999-12-17T08:00:00Z"}}</span>
<span id="cb43-11"><a href="#cb43-11" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.7"
id="greater-than-operator-gt"><span
class="header-section-number">4.7</span> GREATER THAN
operator ($gt)</h2>
<p>(Supports dates and numbers only)</p>
<div class="sourceCode" id="cb44"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb44-1"><a href="#cb44-1" aria-hidden="true" tabindex="-1"></a>Numbers</span>
<span id="cb44-2"><a href="#cb44-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb44-3"><a href="#cb44-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb44-4"><a href="#cb44-4" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$gt": 10000}</span>
<span id="cb44-5"><a href="#cb44-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb44-6"><a href="#cb44-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb44-7"><a href="#cb44-7" aria-hidden="true" tabindex="-1"></a>Dates</span>
<span id="cb44-8"><a href="#cb44-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb44-9"><a href="#cb44-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb44-10"><a href="#cb44-10" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$gt": {"$date":"1999-12-17T08:00:00Z"}}</span>
<span id="cb44-11"><a href="#cb44-11" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.8"
id="greater-than-or-equals-operator-gte"><span
class="header-section-number">4.8</span> GREATER THAN OR
EQUALS operator ($gte)</h2>
<p>(Supports dates and numbers only)</p>
<div class="sourceCode" id="cb45"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb45-1"><a href="#cb45-1" aria-hidden="true" tabindex="-1"></a>Numbers</span>
<span id="cb45-2"><a href="#cb45-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb45-3"><a href="#cb45-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb45-4"><a href="#cb45-4" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$gte": 10000}</span>
<span id="cb45-5"><a href="#cb45-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb45-6"><a href="#cb45-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb45-7"><a href="#cb45-7" aria-hidden="true" tabindex="-1"></a>Dates</span>
<span id="cb45-8"><a href="#cb45-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb45-9"><a href="#cb45-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb45-10"><a href="#cb45-10" aria-hidden="true" tabindex="-1"></a> "HIREDATE": {"$gte": {"$date":"1999-12-17T08:00:00Z"}}</span>
<span id="cb45-11"><a href="#cb45-11" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.9"
id="in-string-operator-instr"><span
class="header-section-number">4.9</span> In string
operator ($instr)</h2>
<p>(Supports strings only)</p>
<div class="sourceCode" id="cb46"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb46-1"><a href="#cb46-1" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb46-2"><a href="#cb46-2" aria-hidden="true" tabindex="-1"></a> "ENAME": {"$instr":"MC"}</span>
<span id="cb46-3"><a href="#cb46-3" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.10"
id="not-in-string-operator-ninstr"><span
class="header-section-number">4.10</span> Not in string
operator ($ninstr)</h2>
<p>(Supports strings only)</p>
<div class="sourceCode" id="cb47"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb47-1"><a href="#cb47-1" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb47-2"><a href="#cb47-2" aria-hidden="true" tabindex="-1"></a> "ENAME": {"$ninstr":"MC"}</span>
<span id="cb47-3"><a href="#cb47-3" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.11" id="like-operator-like"><span
class="header-section-number">4.11</span> LIKE operator
($like)</h2>
<p>(Supports strings. Eescape character not supported to
try to match expressions with _ or % characters.)</p>
<div class="sourceCode" id="cb48"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb48-1"><a href="#cb48-1" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb48-2"><a href="#cb48-2" aria-hidden="true" tabindex="-1"></a> "ENAME": {"$like":"AX%"}</span>
<span id="cb48-3"><a href="#cb48-3" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.12"
id="between-operator-between"><span
class="header-section-number">4.12</span> BETWEEN
operator ($between)</h2>
<p>(Supports string, dates, and numbers)</p>
<div class="sourceCode" id="cb49"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb49-1"><a href="#cb49-1" aria-hidden="true" tabindex="-1"></a>Numbers</span>
<span id="cb49-2"><a href="#cb49-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-3"><a href="#cb49-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb49-4"><a href="#cb49-4" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$between": [1000,2000]}</span>
<span id="cb49-5"><a href="#cb49-5" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb49-6"><a href="#cb49-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-7"><a href="#cb49-7" aria-hidden="true" tabindex="-1"></a>Dates</span>
<span id="cb49-8"><a href="#cb49-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-9"><a href="#cb49-9" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb49-10"><a href="#cb49-10" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$between": [{"$date":"1989-12-17T08:00:00Z"},{"$date":"1999-12-17T08:00:00Z"}]}</span>
<span id="cb49-11"><a href="#cb49-11" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb49-12"><a href="#cb49-12" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-13"><a href="#cb49-13" aria-hidden="true" tabindex="-1"></a>Strings</span>
<span id="cb49-14"><a href="#cb49-14" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-15"><a href="#cb49-15" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb49-16"><a href="#cb49-16" aria-hidden="true" tabindex="-1"></a> "ENAME": {"$between": ["A","C"]}</span>
<span id="cb49-17"><a href="#cb49-17" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb49-18"><a href="#cb49-18" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-19"><a href="#cb49-19" aria-hidden="true" tabindex="-1"></a>Null Ranges ($lte equivalent)</span>
<span id="cb49-20"><a href="#cb49-20" aria-hidden="true" tabindex="-1"></a>(Supported by numbers and dates only)</span>
<span id="cb49-21"><a href="#cb49-21" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-22"><a href="#cb49-22" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb49-23"><a href="#cb49-23" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$between": [null,2000]}</span>
<span id="cb49-24"><a href="#cb49-24" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb49-25"><a href="#cb49-25" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-26"><a href="#cb49-26" aria-hidden="true" tabindex="-1"></a>Null Ranges ($gte equivalent)</span>
<span id="cb49-27"><a href="#cb49-27" aria-hidden="true" tabindex="-1"></a>(Supported by numbers and dates only)</span>
<span id="cb49-28"><a href="#cb49-28" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb49-29"><a href="#cb49-29" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb49-30"><a href="#cb49-30" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$between": [1000,null]}</span>
<span id="cb49-31"><a href="#cb49-31" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.13" id="null-operator-null"><span
class="header-section-number">4.13</span> NULL operator
($null)</h2>
<div class="sourceCode" id="cb50"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb50-1"><a href="#cb50-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb50-2"><a href="#cb50-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"ENAME"</span><span class="fu">:</span> <span class="fu">{</span><span class="dt">"$null"</span><span class="fu">:</span> <span class="kw">null</span><span class="fu">}</span></span>
<span id="cb50-3"><a href="#cb50-3" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<h2 data-number="4.14"
id="not-null-operator-notnull"><span
class="header-section-number">4.14</span> NOT NULL
operator ($notnull)</h2>
<div class="sourceCode" id="cb51"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb51-1"><a href="#cb51-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb51-2"><a href="#cb51-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"ENAME"</span><span class="fu">:</span> <span class="fu">{</span><span class="dt">"$notnull"</span><span class="fu">:</span> <span class="kw">null</span><span class="fu">}</span></span>
<span id="cb51-3"><a href="#cb51-3" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<h2 data-number="4.15" id="and-operator-and"><span
class="header-section-number">4.15</span> AND operator
($and)</h2>
<p>(Supports all operators, including $and and $or)</p>
<div class="sourceCode" id="cb52"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb52-1"><a href="#cb52-1" aria-hidden="true" tabindex="-1"></a>(Example: Salary greater than 1000 and name starts with S or T)</span>
<span id="cb52-2"><a href="#cb52-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb52-3"><a href="#cb52-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb52-4"><a href="#cb52-4" aria-hidden="true" tabindex="-1"></a> "SALARY": {"$gt": 1000},</span>
<span id="cb52-5"><a href="#cb52-5" aria-hidden="true" tabindex="-1"></a> "ENAME": {"$or": [{"$like":"S%"}, {"$like":"T%"}]}</span>
<span id="cb52-6"><a href="#cb52-6" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb52-7"><a href="#cb52-7" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb52-8"><a href="#cb52-8" aria-hidden="true" tabindex="-1"></a>Invalid expression (operators $lt and $gt lack column context)</span>
<span id="cb52-9"><a href="#cb52-9" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb52-10"><a href="#cb52-10" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb52-11"><a href="#cb52-11" aria-hidden="true" tabindex="-1"></a> "$and": [{"$lt": 5000},{"$gt": 1000}]</span>
<span id="cb52-12"><a href="#cb52-12" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb52-13"><a href="#cb52-13" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb52-14"><a href="#cb52-14" aria-hidden="true" tabindex="-1"></a>Valid alternative for the previous invalid expression</span>
<span id="cb52-15"><a href="#cb52-15" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb52-16"><a href="#cb52-16" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb52-17"><a href="#cb52-17" aria-hidden="true" tabindex="-1"></a> "$and": [{"SALARY": {"$lt": 5000}}, {"SALARY": {"$gt": 1000}}]</span>
<span id="cb52-18"><a href="#cb52-18" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h2 data-number="4.16" id="or-operator-or"><span
class="header-section-number">4.16</span> OR operator
($or)</h2>
<p>(Supports all operators including $and and $or.
Similar to High order AND)</p>
<div class="sourceCode" id="cb53"><pre
class="sourceCode txt"><code class="sourceCode default"><span id="cb53-1"><a href="#cb53-1" aria-hidden="true" tabindex="-1"></a>(Example: name starts with S or salary greater than 1000)</span>
<span id="cb53-2"><a href="#cb53-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb53-3"><a href="#cb53-3" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb53-4"><a href="#cb53-4" aria-hidden="true" tabindex="-1"></a> "$or": [{"SALARY":{"$gt": 1000}},{"ENAME": {"$like":"S%"}}]</span>
<span id="cb53-5"><a href="#cb53-5" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<!-- Copyright (c) 2025, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="5" id="authenticate-a-rest-user"><span
class="header-section-number">5</span> Authenticate a
REST User</h1>
<p>When executing CRUD operations on a REST object that
requires authentication, one must first authenticate a
REST user via the corresponding linked authentication
app.</p>
<h2 data-number="5.1"
id="mysql-internal-authentication"><span
class="header-section-number">5.1</span> MySQL Internal
Authentication</h2>
<p>In the case of a MySQL Internal auth app, the
workflow is as follows. First the client must send the
credentials to the <code>/login</code> path at the
corresponding authentication endpoint, by omission
<code>/${service}/authentication/login</code>. The
client specifies the auth mechanism to use (cookie or
bearer token). By default, the authentication mechanism
used is based on a cookie.</p>
<p>Pattern:</p>
<pre><code>POST http://<HOST>:<PORT>/<ServiceAlias>/<AuthPath>/login
{
"username": "...",
"password": "...",
"authApp": "...",
"sessionType": "cookie | bearer"
}</code></pre>
<p>If the client requests a cookie and the
authentication succeeds, the MRS backend server
immediately sends back a response containing the
corresponding <code>Set-Cookie</code> header:</p>
<p>Example:</p>
<pre><code>curl -i -X POST -d \{\"username\":\"...\",\"password\":\"...\",\"authApp\":\"MySQL\",\"sessionType\":\"cookie\"} https://localhost:8000/mrs/authentication/login
HTTP/1.1 200 Ok
...
Set-Cookie: session_31000000000000000000000000000000=XXXXXXXXXXXXXXXXXXX; Path=/; SameSite=None; Secure; HttpOnly</code></pre>
<p>If the client requests a bearer token, instead of the
<code>Set-Cookie</code> header the response includes a
JSON object in the body with an <code>accessToken</code>
property whose value matches the generated JWT.</p>
<p>Example:</p>
<pre><code>curl -i POST -d \{\"username\":\"...\",\"password\":\"...\",\"authApp\":\"MySQL\",\"sessionType\":\"bearer\"\} "https://localhost:8443/mrs/authentication/login"
HTTP/1.1 200 Ok
...
Content-Type: application/json
...
{"accessToken":"..."}</code></pre>
<h2 data-number="5.2" id="mrs-authentication"><span
class="header-section-number">5.2</span> MRS
Authentication</h2>
<p>In the case of an MRS auth app, the workflow follows
a <a
href="https://datatracker.ietf.org/doc/html/rfc5802">SCRAM</a>-style
negotiation. First the client must send the credentials
to the authentication endpoint. In this case, instead of
the original password, the client generates and sends
the initial nonce (a random hex string). Similarly, the
client also specifies the auth mechanism to use (cookie
or bearer token). Then the MRS backend server sends back
a response containing a JSON object in the body which
includes the value of all variables required to
calculate the SCRAM client proof.</p>
<p>Pattern:</p>
<pre><code>POST http://<HOST>:<PORT>/<ServiceAlias>/<AuthPath>/login
{
"username": "...",
"nonce": "...",
"authApp": "...",
"sessionType": "cookie | bearer"
}
POST http://<HOST>:<PORT>/<ServiceAlias>/<AuthPath>/login
{
"clientProof": [...],
"nonce": "...",
"state": "response"
}</code></pre>
<p>Example:</p>
<pre><code>curl -i -X POST -d \{\"username\":\"...\",\"nonce\":\"...\",\"authApp\":\"MRS\",\"sessionType\":\"cookie\"} https://localhost:8000/mrs/authentication/login
...
{"session":"...","iterations":5000,"nonce":"...","salt":[...]}</code></pre>
<p>To finish the SCRAM negotiation, the client needs to
calculate the corresponding client proof send another
request to the same endpoint containing that value,
alongside the server-generated nonce and an additional
<code>state</code> flag set to <code>response</code>. If
the authentication succeeds, the MRS backend server
sends back a response containing the
<code>Set-Cookie</code> header.</p>
<p>Example:</p>
<pre><code>curl -i -X POST -d \{\"clientProof\":[...],\"nonce\":\"...\",\"state\":\"response\"\} https://localhost:8000/mrs/authentication/login
HTTP/1.1 200 Ok
...
Set-Cookie: session_30000000000000000000000000000000=XXXXXXXXXXXXXXXXXXX; Path=/; SameSite=None; Secure; HttpOnly</code></pre>
<p>If the client requests a bearer token instead, the
process is exactly the same apart from the last step
where, the backend server sends back a response
containing JSON object in the body with an
<code>accessToken</code> property whose value matches
the generated JWT.</p>
<h2 data-number="5.3" id="oauth"><span
class="header-section-number">5.3</span> OAuth</h2>
<p>In the case of an OAuth auth app, an hypermedia
client like a web-browser is required, because in the
initial step, the client is redirected by the MRS
backend server to the authentication web page of the
actual auth provider (OCI, Google or Facebook).
Currently, only cookie-based authentication is
supported.</p>
<p>Example:</p>
<pre><code>curl -iG https://localhost:8443/mrs/authentication/login --data-urlencode 'authApp=Facebook'
HTTP/1.1 307 Temporary Redirect
...
Location: https://www.facebook.com/v12.0/dialog/oauth?response_type=code&state=first&client_id=XXXXXXXXXXXXX&redirect_uri=https://localhost:8443/mrs/authentication/login?authApp=Facebook</code></pre>
<p>If the authentication succeeds, the auth provider
redirects the client to the corresponding callback url
sending the authorization code to the MRS backend
server. The MRS backend server redirects the client to a
different URL under its control and then sends back a
response containing the <code>Set-Cookie</code>
header.</p>
<p>Example:</p>
<pre><code>curl -i https://localhost:8443/mrs/authentication/login --data-urlencode 'access_token=YYYYYYYYYYYY' --data-urlencode 'redirect_url=https://localhost:8443/mrs/success'
HTTP/1.1 307 Temporary Redirect
...
Location: https://www.facebook.com/v12.0/dialog/oauth?response_type=code&state=first&client_id=XXXXXXXXXXXXX&redirect_uri=https://localhost:8443/mrs/authentication/login?authApp=Facebook
curl -i https://localhost:8443/mrs/success
HTTP/1.1 200 Ok
...
Set-Cookie: session_32000000000000000000000000000000=XXXXXXXXXXXXXXXXXXX; Path=/; SameSite=None; Secure; HttpOnly</code></pre>
<blockquote>
<p>The examples are for illustrative purposes only, the
workflow is entirely handled by the hypermedia client
(web browser).</p>
</blockquote>
<h2 data-number="5.4"
id="executing-crud-operations-on-a-rest-object"><span
class="header-section-number">5.4</span> Executing CRUD
Operations on a REST Object</h2>
<p>Once the authentication succeeds, one can execute the
CRUD operations enabled for a specific REST object that
requires authentication, by including an additional
header in the corresponding HTTP request. In the case of
cookie-based authentication, the value of the
<code>Set-Cookie</code> response header should be used
in the <code>Cookie</code> request header.</p>
<p>Pattern:</p>
<pre><code>Cookie: <Set-Cookie></code></pre>
<p>Example:</p>
<pre><code>curl -H "Cookie: session_31000000000000000000000000000000=XXXXXXXXXXXXXXXXXXX; Path=/; SameSite=None; Secure; HttpOnly" https://localhost:8443/mrs/sakila/actor</code></pre>
<p>In the case of token-based authentication, the JWT
should be encoded in the <code>Authorization</code>
request header as follows:</p>
<p>Pattern:</p>
<pre><code>Authorization: Bearer XXXXXXXXXXXXXXXXX</code></pre>
<p>Example:</p>
<pre><code>curl -H "Authorization: Bearer XXXXXXXXXXXXXXXXX" https://localhost:8443/mrs/sakila/actor</code></pre>
<h3 data-number="5.4.1" id="full-workflow-example"><span
class="header-section-number">5.4.1</span> Full Workflow
Example</h3>
<p>In the worst-case scenario, when the user is not yet
authenticated, executing a CRUD operation on a protected
REST object requires two steps which should share some
kind of state, i.e. either the cookie or the bearer
token need to be saved somewhere in between.</p>
<p>When requesting a cookie, the value of the
<code>Set-Cookie</code> header can be extracted using
<code>curl</code>:</p>
<pre><code>$ cookie=$(curl -X POST -s -o /dev/null -w '%header{set-cookie}' -d \{\"username\":\"...\",\"password\":\"...\",\"authApp\":\"MySQL\",\"sessionType\":\"cookie\"\} "https://localhost:8443/mrs/authentication/login")
$ curl -H "Cookie: $cookie" https://localhost:8443/mrs/sakila/actor</code></pre>
<p>When requesting a bearer token, since the JWT is sent
in a JSON object in the response body, it can be
extracted using a tool like <a
href="https://jqlang.org/"><code>jq</code></a>:</p>
<pre><code>$ jwt=$(curl -X POST -s -d \{\"username\":\"...\",\"password\":\"...\",\"authApp\":\"MySQL\",\"sessionType\":\"bearer\"\} "https://localhost:8443/mrs/authentication/login" | jq -r .accessToken)
$ curl -H "Authorization: Bearer $jwt" https://localhost:8443/mrs/sakila/actor</code></pre>
<p>Copyright (c) 2022, 2025, Oracle and/or its
affiliates.</p>
</div>
</div>
</div>
</body>
</html>