# Filtering

The search API uses the following filtering options to facilitate the search and allow to perform a wide range of consultations.

Several techniques solve the filtering issue when you're querying over a RESTful interface. For example, you can use standard HTTP parameters to add filtering capabilities to your query. It's pretty simple but doesn't cover complex needs. We require a SQL-like approach, with typical operators like `AND`, `OR`, `EQUAL`, `NOT EQUAL`, etc. OpenGate allows you to filter your queries by sending a `POST` request to a specific URI. In the `POST` request, you must send a JSON document with a fashionable DSL structure. It is a command pattern approach in contrast with the entity/collection pattern used in the provisioning API.

## Filtering operators

### Filtering comparison operator list

- `eq`: Equals.
- `neq`: Not equals.
- `like`: Regex pattern like.
- `gt`: Greater than.
- `lt`: Lower than.
- `gte`: Greater than or equals.
- `lte`: Lower than or equals.
- `in[]`: Included in a concrete group.
- `nin[]`: Not included in a concrete group.
- `exists`: Exists.
- `within`: Included in an `areas.geometry` GeoJson (exclusive for Area search).

{{% expand title="See supported identifiers for existing comparison operator." expanded="false" %}}

- **asset:** identifier
- **channel:** identifier
- **device:**
  - identifier
  - serialNumber
- **device.communicationModules[]:**
  - identifier
  - mobile.imei
- **device.communicationModules[].subscriber:**
  - identifier
- **device.communicationModules[].subscription:**
  - identifier
  - address
  - mobile.imsi
  - mobile.msisdn
  - mobile.icc
  - presence.ipRtt
  - presence.unifiedPresence
- **entity:**
  - areas
  - location
- **organization:** identifier
- **provision.asset:**
  - identifier
  - location
- **provision.device:**
  - identifier
  - location
- **provision.device.communicationModules[]:** identifier
- **provision.device.communicationModules[].subscriber:** identifier
- **provision.device.communicationModules[].subscription:**
  - identifier
  - address
  - mobile
  - imsi
  - mobile
  - msisdn
  - mobile.icc
- **provision.organization:**
  - identifier
  - plan
- **provision.ticket:**
  - identifier
  - location
  - owner
  - assignee
  - specificType
  - section
  - entity
  - assignedDate
  - answeredDate
  - updatedDate
  - restorationDate
  - resolutionDate
  - closedDate
  - parentTicket

{{% /expand %}}

### Filtering logical operators list

- `and[]`: And
- `or[]`: Or

## How to use filters

Let's suppose we want to filter devices with `device.operationalStatus` equals to `NORMAL` and with `device.communicationModules[].mobile.imei` starting with `351873000102290`.

If we were dealing with a SQL database we'd write the following SQL sentence:

```sql
SELECT * FROM device
WHERE device.operationalStatus LIKE 'NORMAL'
AND device.communicationModules[].mobile.imei LIKE '351873000102290'
```

{{% notice style="info" title="Note" icon="lightbulb" %}}
Remember, you can use all the data streams defined in the [default data models](../../../management/organizations/data_models/default_catalog) and your own data streams in the `WHERE` clause.
{{% /notice %}}

Translating the previous SQL sentence to OpenGate searching API we'll have:

```json
{
  "filter": {
    "and": [
      {
        "like": {
          "provision.device.administrativeState": "NORMAL"
        }
      },
      {
        // The result will contain all devices with collected operational Status that
        // contains NORMAL and are related with communications modules with collected
        // imei containing 351873000102290
        "like": {
          "provision.device.communicationModules[].mobile.imei": "351873000102290"
        }
      }
    ]
  }
}
```

The result will contain all devices with collected operational Status that contains `NORMAL` and are related to communications modules with collected `imei` containing `351873000102290`.

Another example comparing SQL to JSON, searching all devices except the one with `serialNumber` equal to `82A75D494B0EBF7A95587285AE78E83F`:

```sql
SELECT * FROM device WHERE serialNumber <> '82A75D494B0EBF7A95587285AE78E83F'
/north/v80/search/devices
```

```json
{
  "filter": {
    "neq": { "device.serialNumber": "82A75D494B0EBF7A95587285AE78E83F" }
  },
  "limit": {
    "start": 2
  }
}
```

### More filtering examples

Click on any of the following links to expand the contents.

{{% expand title="Searching using `in` and `nin` operators like an aggregation of ids" %}}

```json
{
  "filter": {
    "and": [
      { "in": { "device.name": ["device_1", "device_2"] } },
      { "nin": { "device.description": ["device default description"] } }
    ]
  },
  "limit": {
    "start": 26,
    "size": 50
  }
}
```

{{% /expand %}}

{{% expand title="Searching using the `tag` attribute" %}}

```json
{
  "filter": {
    "eq": { "tag": "my_sticky_tag" }
  },
  "limit": {
    "start": 26,
    "size": 50
  }
}
```

{{% /expand %}}

{{% expand title="Searching for collected hardware fields" %}}

```json
{
  "filter": {
    "and": [
      { "like": { "device.model": "EF5" } },
      { "exists": { "device.identifier": true } }
    ]
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}
```

{{% /expand %}}

{{% expand title="Searching for software field (device whose software has this regex chain)" %}}

```json
{
  "filter": {
    "like": { "device.software": "1.0" }
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}
```

{{% /expand %}}

{{% expand title="Retrieve all the operations in the catalog applicable to the Subscription & Asset entities" %}}

```json
{
  "filter": {
    "in": { "operationEntityType": ["SUBSCRIPTION", "ASSET"] }
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}
```

{{% /expand %}}

{{% expand title="Searching for device alarms, /north/v80/search/entities/devices/alarms" %}}

```json
{
  "filter": {
    "in": { "device.name": ["DEVICE22"] }
  },
  "limit": {
    "start": 1,
    "size": 10
  }
}
```

{{% /expand %}}

{{% expand title="Searching for a particular data of a complex value" %}}

```json
{
  "filter": {
    "like": {
      "provision.device.location._current.value.postal": "41015"
    }
  }
}
```

{{% /expand %}}