Query Language
Introduction
OMS defines a standard way to encode search criteria for some queries. It meant to provide a mechanism to query data from OMS in a simple, database agnostic and extensible way.
Parse Tree
Parse Tree is a set of two simple structures which allow to encode any expression. All other expressions are build on top of these structures.
constant value
Constant value - represents a simple constant value that can be used as an argument to an expression. In JSON every value encoded as object with two fields:
- value - a string representation of constant value
- type - type of the value (string is used if undefined or null)
Example
{"value": "20", "type": "int"}
expression
Expression - represents a logical expression over some arguments. Different expressions have different number of arguments and may have additional requirements to these arguments. In JSON every expression encoded as object with two fields:
- exp - type of the expression
- args - an array of other expressions or constant values
Example
{"exp": "SUM", "args": [{"value": 10}, {"value": 20}]}
Query Language
Query Language is a set of standard expressions for Parse Tree.
Values
Value represents constants in your expressions. Every value has a type, if type is not specified value considered to be a string.
JSON Example
{"value": "foo", "type": "string"}
SQL equivalent
WHERE "foo"
Types
| Type | Description | Example value |
|---|---|---|
| string | String value | foo |
| integer | Integer value | 91 |
| float | Float value | 99.31 |
| boolean | Boolean value, 0 used for false and 1 used for true | 0 |
| datetime | Date and Time encoded in ISO-8601 date time format | 2005-08-15T15:52:01+00:00 |
| blob | Binary data encoded in Base64 | TWFnZW50bw== |
Expressions
field
This expression represents a field in the document. It should have a single value argument which contains field name.
JSON Example
{"exp": "field", "args": [{"value": "foo"}]}
SQL equivalent
WHERE `foo`
array
This expression represents a list of constant values. It can have any number of value arguments. Every argument represents an item of the array.
JSON Example
{"exp": "array", "args": [
{"value": "foo"},
{"value": "bar"}
]}
SQL equivalent
WHERE ("foo", "bar")
all
This expression used to ensure all sub-expressions are true. It can have any number of expression arguments. Every argument represents a sub-expression.
JSON Example
{"exp": "all", "args": [
{"exp": "eq", "args": [{"exp": "field", "args": [{"value": "first_name"}]}, {"value": "John"}]},
{"exp": "eq", "args": [{"exp": "field", "args": [{"value": "last_name"}]}, {"value": "Doe"}]}
]}
SQL equivalent
WHERE (... AND ...)
any
This expression used to ensure any (at least one) of sub-expressions are true. It can have any number of expression arguments. Every argument represents a sub-expression.
JSON Example
{"exp": "any", "args": [
{"exp": "eq", "args": [{"exp": "field", "args": [{"value": "first_name"}]}, {"value": "John"}]},
{"exp": "eq", "args": [{"exp": "field", "args": [{"value": "last_name"}]}, {"value": "Doe"}]}
]}
SQL equivalent
WHERE (... OR ...)
not
This expression used to ensure that inner expression is false. It should always have only one expression argument.
JSON Example
{"exp": "not", "args": [
{"exp": "is_empty", "args": [{"exp": "field", "args": [{"value": "email"}]}]}
]}
SQL equivalent
WHERE NOT(...)
eq / neq
These expressions used to ensure that two operands are equal (eq) or not equal (neq). It should always have two arguments and at least one of them should be a field and other should be value arguments.
JSON Example
{
"exp": "eq",
"args": [
{"exp": "field", "args": [{"value": "qty"}]},
{"value": "0", "type": "int"}
]
}
SQL equivalent
WHERE `qty` = 0
gt / ge / lt / le
These expressions used to compare two operands: left greater than right (gt), left greater or equal to the right (ge), left less than right (lt) or left less or equal to the right (le). It should always have two arguments and at least one of them should be a field and other should be value arguments.
JSON Example
{
"exp": "lt",
"args": [
{"exp": "field", "args": [{"value": "qty"}]},
{"value": "100", "type": "int"}
]
}
SQL equivalent
WHERE `qrt` <= 0
contains
This expression used to ensure that left operand contains right operand. It should always have two arguments, first should always be a field expression and second should be a value argument.
JSON Example
{
"exp": "contains",
"args": [
{"exp": "field", "args": [{"value": "product_name"}]},
{"value": "shirt"}
]
}
SQL equivalent
WHERE CONTAINS(`product_name`, "shirt")
in / not_in
These expressions used to ensure a value of the field is one of the given (in) or not one of them (not_in). It should always have two arguments, first should always be a field expression and second should be an array expression.
JSON Example
{
"exp": "in",
"args": [
{"exp": "field", "args": [{"value": "stock_type"}]},
{"exp": "array", "args": [
{"value": "good"},
{"value": "second_choice"}
]}
]
}
SQL equivalent
WHERE `stock_type` IN ("good", "second_choice")
is_null / is_not_null
These expressions used to ensure a value is null, or does not exists (is_null) or is not null or does exists (is_not_null). It should always have one expression argument.
JSON Example
{
"exp": "is_null",
"args": [{"exp": "field", "args": [{"value": "email"}]}]
}
SQL equivalent
WHERE `email` IS NULL
is_empty / is_not_empty
These expressions used to ensure a value is null, an empty string, zero or does not exists (is_empty) or is not null, non empty string, non zero or does exists (is_not_empty). It should always have one expression argument.
JSON Example
{
"exp": "is_empty",
"args": [{"exp": "field", "args": [{"value": "first_name"}]}]
}
SQL equivalent
WHERE `first_name` = ""
Usage
Query language is used in search methods of Magento API, it is part of magento.common.query data structure. Below you will find examples of Magento API calls which use query language.
Examples
JSONRPC request to fetch orders with status “LOGISTICS”
Example
{
"jsonrpc": "2.0",
"id": "1",
"method": "magento.sales.order_repository.search",
"params": {
"query": {
"size": 20,
"criteria": {
"exp": "eq",
"args": [
{"exp": "field", "args": [{"value": "status"}]},
{"value": "LOGISTICS"}
]
}
}
}
}
Fetching second page of the result for query above
Example
{
"jsonrpc": "2.0",
"id": "1",
"method": "magento.sales.order_repository.search",
"params": {
"query": {
"offset": 20,
"size": 20,
"criteria": {
"exp": "eq",
"args": [
{"exp": "field", "args": [{"value": "status"}]},
{"value": "LOGISTICS"}
]
}
}
}
}
Adding sorting by origin date to the query
Example
{
"jsonrpc": "2.0",
"id": "1",
"method": "magento.sales.order_repository.search",
"params": {
"query": {
"size": 20,
"order": {"origin_date": "DESC"},
"criteria": {
"exp": "eq",
"args": [
{"exp": "field", "args": [{"value": "status"}]},
{"value": "LOGISTICS"}
]
}
}
}
}
Extending search criteria, searching for orders placed after 1st of Jan 2017 and with status “LOGISTICS”
Example
{
"jsonrpc": "2.0",
"id": "1",
"method": "magento.sales.order_repository.search",
"params": {
"query": {
"criteria": {
"exp": "all",
"args": [
{
"exp": "eq",
"args": [
{"exp": "field", "args": [{"value": "status"}]},
{"value": "LOGISTICS"}
]
},
{
"exp": "ge",
"args": [
{"exp": "field", "args": [{"value": "origin_date"}]},
{"value": "2017-01-01T00:00:00+00:00", "type": "datetime"}
]
}
]
}
}
}
}
Get all orders that had the order status LOGISTICS between two dates using history_status and history_date. See Order Statuses for filterable values:
Example
{
"jsonrpc": "2.0",
"id": "1",
"method": "magento.sales.order_repository.search",
"params": {
"query": {
"criteria": {
"exp": "all",
"args": [
{
"exp": "eq",
"args": [
{"exp": "field", "args": [{"value": "history_status"}]},
{"value": "LOGISTICS"}
]
},
{
"exp": "ge",
"args": [
{"exp": "field", "args": [{"value": "history_date"}]},
{"value": "2018-01-20T00:00:00+00:00", "type": "datetime"}
]
},
{
"exp": "lt",
"args": [
{"exp": "field", "args": [{"value": "history_date"}]},
{"value": "2018-01-21T23:59:59+00:00", "type": "datetime"}
]
}
]
}
}
}
}