Advanced Filter

Advanced filter with multiple criteria, limit, offset, order

Filters

The main entry point into advanced filtering is the parameter filters, as a value of this parameter the filter is given to the API. We'll describe the details in combination with the following example - the filter parameter is represented as an array.

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 1 criterion is given. Each criterion needs to be wrapped in an array again (see examples below with multiple criteria). Per criteria, you can specify a mode, which will indicate how the criterium groups will be compared (see Mode for more information).

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

column - A column name

operator - One of the supported operators

value - Any primitive value, arrays included

There can be as many criteria and criterium-groups sets as needed, there are no limits.

Additional settings

On the same level as the filters attribute several other attributes can be specified to control the output, such as:

mode - this 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 amount of products returned

orderBy - determines the ordering of the products Values should consist of an existing column and a direction either ASC or DESC

groupBy - it will return unique values for the column on which is grouped. Value should be an existing column

Supported Operators

This is a list of value comparison operators usable in the criterium groups.

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 will select 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 will select all customers who have 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