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”

{
    "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

{
    "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

{
    "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”

{
    "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:

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