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