Advanced Filter

The advanced filter lets you build complex queries with multiple criteria sets, ordering, and pagination.

Advanced filter with multiple criteria, limit, offset, order

Filters

The filters parameter is the main entry point into advanced filtering. It takes an array. The following example demonstrates the filter structure:

Advanced filter example 1

[
    'filters' => [
        [
            'criteria' => [
                [
                    'column' => 'name',
                    'operator' => '=',
                    'value' => 'Josianne Berge',
                ],
                [
                    'column' => 'email',
                    'operator' => '=',
                    'value' => 'nicolas.estrella@yahoo.com',
                ],
            ],
        ],
    ],
    ...
]

The filters parameter consists of one or more sets of criteria. In the example above, one criterion set is given. Each criterion set must be wrapped in its own array. For each criterion set, you can specify a mode that controls how the criterium groups are compared.

The criteria can consist of one or more criterium groups. In the example above, two criterium groups are given. Each criterium group has the following required attributes:

column — A column name.

operator — One of the supported operators.

value — Any primitive value, including arrays.

There is no limit on the number of criteria sets or criterium groups.

Additional settings

At the same level as the filters attribute, you can specify additional attributes to control the output:

mode — Indicates how the criterium groups are concatenated. Valid values are AND (default) and OR.

offset — The position of the first product in the total product set.

limit — The total number of products returned.

orderBy — Determines the ordering of products. Values must consist of an existing column and a direction: ASC or DESC.

groupBy — Returns unique values for the grouped column. Value must be an existing column.

Supported operators

The following operators are available for criterium group comparisons:

Operator
BETWEEN
=
>=
>
ILIKE
IN
IS
LIKE
NOT BETWEEN
!=
NOT ILIKE
NOT IN
IS NOT
NOT LIKE
<=
<

Basic example

[
    'filters' => [
        [
            'criteria' => [
                [
                    'column' => 'id',
                    'operator' => 'IN',
                    'value' => ['d25c925622fd6218aae16a8913e73b7d', '32f1ce5593d13cb2a36cb57b943eb2b3'],
                ],
            ],
        ],
        [
            'criteria' => [
                [
                    'column' => 'color',
                    'operator' => '=',
                    'value' => 'White',
                ],
                [
                    'column' => 'countryCode',
                    'operator' => '=',
                    'value' => 'MN',
                ],
            ],
            'mode' => 'AND',
        ],
        [
            'criteria' => [
                [
                    'column' => 'name',
                    'operator' => '=',
                    'value' => 'Josianne Berge',
                ],
                [
                    'column' => 'email',
                    'operator' => '=',
                    'value' => 'nicolas.estrella@yahoo.com',
                ],
            ],
            'mode' => 'OR',
        ],
    ],
    'mode' => 'OR',
    'offset' => 0,
    'limit' => 10,
    'orderBy' => ['color' => 'ASC'],
    'groupBy' => null,
]

SQL equivalent

SELECT * FROM data WHERE
(id IN ('d25c925622fd6218aae16a8913e73b7d', '32f1ce5593d13cb2a36cb57b943eb2b3')) OR
(color = 'White' AND countryCode = 'MN') OR
(name = 'Josianne Berge' OR email = 'nicolas.estrella@yahoo.com')
ORDER BY color ASC LIMIT 10 OFFSET 0

cURL example

curl --location -g \
--request GET 'https://export-api.productsup.com/sites/522103/channels/302481/destinations/154110/products?filters[0][criteria][0][column]=color&filters[0][criteria][0][operator]==&filters[0][criteria][0][value]=White&filters[0][criteria][1][column]=countryCode&filters[0][criteria][1][operator]==&filters[0][criteria][1][value]=MN&filters[0][mode]=AND&orderBy[color]=ASC&filters[1][criteria][0][column]=name&filters[1][criteria][0][operator]==&filters[1][criteria][0][value]=Josianne%20Berge&filters[1][mode]=AND&mode=OR' \
--header 'X-EA-Auth-Token: kx0g3jwi0121jwj12j1x'

JSON response

[
    {
        "color": "White",
        "countryCode": "MN",
        "email": "dallin80@hayes.com",
        "id": "32f1ce5593d13cb2a36cb57b943eb2b3",
        "name": "Reymundo Marvin Jr."
    },
    {
        "color": "White",
        "countryCode": "MN",
        "email": "brown.aurelia@yahoo.com",
        "id": "d25c925622fd6218aae16a8913e73b7d",
        "name": "Prof. Michael Morar II"
    },
    {
        "color": "White",
        "countryCode": "MN",
        "email": "allene62@gmail.com",
        "id": "a7b12775183a791f43d5a0c86ebf7569",
        "name": "Arianna Stark DVM"
    },
    {
        "color": "White",
        "countryCode": "MN",
        "email": "shakira.leffler@wolf.net",
        "id": "19d8130c94318c7da128d88c0d5b25d2",
        "name": "Reyna Medhurst"
    },
    {
        "color": "WhiteSmoke",
        "countryCode": "VU",
        "email": "nicolas.estrella@yahoo.com",
        "id": "78f3b1281fd2f3692ca6387fe7899816"
    }
]

Additional examples

Selecting products with certain colors

The following request selects products with the color Crimson, Aqua, GreenYellow, or Violet.

curl --location -g \
--request GET 'https://export-api.productsup.com/sites/522103/channels/302481/destinations/154110/products?filters[0][criteria][0][column]=color&filters[0][criteria][0][operator]=IN&filters[0][criteria][0][value][]=Crimson&filters[0][criteria][0][value][]=Aqua&filters[0][criteria][0][value][]=GreenYellow&filters[0][criteria][0][value][]=Violet&orderBy[color]=ASC&mode=OR' \
--header 'X-EA-Auth-Token: kx0g3jwi0121jwj12j1x'

Selecting all customers with a certain email address

The following request selects all customers with a Yahoo email address.

curl --location -g \
--request GET 'https://export-api.productsup.com/sites/522103/channels/302481/destinations/154110/products?filters[0][criteria][0][column]=email&filters[0][criteria][0][operator]=LIKE&filters[0][criteria][0][value]=%yahoo.com&orderBy[color]=ASC&mode=OR' \
--header 'X-EA-Auth-Token: kx0g3jwi0121jwj12j1x'

Advanced Filter

On this page