General operators

Equality

Checks for exact equality or inequality.

Operators:
- eq: equality
- ne: inequality
Type: the same type as the attribute we are checking against.

Examples:

{
    "body": {
        "eq": "crowd.dev"
    },
     "score": {
        "ne": 0
    },
}

In the case of equality, we can also omit the operator:

{
    "body": "Exact activity body."
}

Nullity

Checks whether an attribute is null. This operator is always sent as a string value.

Operator: "NULL"
Examples:
```
{
    "body": "NULL"
}
```

In

Checks if the attribute is in an array.

Type: Array of the same type as the attribute.
Operator: in

Example:

{
    "type": {
        "in": ["message", "comme"]
    }
}

Composition

And/Or

We can use and, or, to compose any single-attribute query into a complex query

Operators: and and or.
Type: Array of all the conditions that are being composed

Example type == "message" && (platform == "discord" || platform == "slack"):

{
    "and": [.  
        {"type": "message"},
        "or": [.
            {"platform": "slack"},
            {"platform": "discord"}
        ]
    ]
}

We can compose complex queries using and, or, and not operators.

Negation

We can use the negation operator to negate statements.

Operator: not

Example `!(type == "message" && (platform == "discord" || platform == "slack"):

{
    "not": {
        "and": [.  
            {"type": "message"},
            "or": [.
                {"platform": "slack"},
                {"platform": "discord"}
            ]
        ]
    }
}

Type-specific operators

String operators

Contains

Checks if a text field contains the string sent. Case-independent.

Type: string
Operator: textContains

Example:

{
    "body": {
        "textContains": "crowd.dev"
    }
}

Example matches on Welcome to crowd.dev, the community-led growth platform for developer-first companies:

textContains: welcome to crowd.dev: ✔️
textContains: crowd.dev: ✔️
textContains: community building: ✘

Regular expressions

Checks if a text field matches the regular expression sent. Case-independent.

Type: string
Operator: regexp

Example:

{
    "body": {
        "regexp": "([A-Z])\w+"
    }
}

Example matches on Capitalized:

regexp: ([A-Z])\w+: ✔️
regexp: ([1-9])+: ✘

Number and Date operators

Numerical types and dates support the same operators.

Magnitude comparison

Make value comparisons between dates or numbers. We need to send a date or number as a comparison.

Operators:
- gte: greater-than or equal
- gt: greater than
- lte: lower-than or equal
- lt: lower-than
Type: number or date-time

Example:

{
    "activityCount": {
        "gte": 1
    }
}

Between

Check whether a date or a number is within a range.

Operators: between
Type: Pair of number or date-time.

Examples:

{
    "activityCount": {
        "between": [10, 20]
    }
}

Example matches on 10:

between: [1, 100]: ✔️
between: [1, 10]: ✔️
between: [1, 2]: ✘

Arrays

Contains

Checks if an array contains the sent array. Every element of the comparison array must be in the attribute array.

Type: Array
Operator: contains

Example:

{
    "keywords": {
        "contains": ["community", "open source"]
    }
}

We can also use it to check for a single element.

{
    "keywords": {
        "contains": ["community"]
    }
}

Example matches on [community, open source, developers]":

contains: ["community"]: ✔️
contains: ["community", "open source"]: ✔️
`contains: ["community", "building"]: ✘

Overlap

Checks if an array has any overlap with the sent array. Every element of the comparison array must be in the attribute array.

Type: Array
Operator: overlap

Example:

{
    "keywords": {
        "overlap": ["community", "open source"]
    }
}

Example matches on [community, open source, developers]":

overlap: ["community", "open source"]: ✔️
`overlap: ["community", "building"]: ✔️
overlap: ["building", "software"]: ✘

JSON operators

When querying JSON fields, we can send nested keys if we know the key. For example, if we want to filter by the username object in members, ´in the key github, we could send:

{
    "username.github": "gilfoyle"
}

However, sometimes we might want to filter on the whole username object. For example, if we want to know if the username gilfoyle exists in any platform.

Contains

It converts the JSON to a string, and it checks if the stringified JSON contains the query string

Type: string
Operator: jsonContains

Example:

{
    "username": {
        "jsonContains": "gilfoyle"
    }
}

Example matches on

{
    "github": "gilfoyle",
    "discord": "a_satanist"
}

jsonContains: gilfoyle: ✔️
jsonContains: a satanist: ✔️
jsonContains: dinesh: ✘

Many-to-Many relations

When querying many-to-many relations, we always need to send a list of IDs. For example:

We want to get all the Tasks that have member m1_id:
```
{
    "members": [m1_id]
}
```
We want to get all the Tasks that have members m1_id or m2_id:
```
{
    "members": [m1_id, m2_id]
}
```

Examples

Founders in Berlin, Barcelona, or Portugal or that have been tagged

Members whose job title contains founder and:
- are located in either Barcelona, Berlin or Portugal, or
- have been tagged with tags whose IDs are tag1ID or tag2ID

{
    "or": [
        {
            "location": {
                "in": [
                    "Barcelona",
                    "Berlin",
                    "Portugal"
                ]
            }
        },
        {
            "tags": [
                "tag1ID",
                "tag2ID"
            ]
        }
    ],
    "jobTitle": {
        "textContains": "founder"
    }
}

COSS organizations with specific revenue and employee ranges

Organizations that have either:
- a minimum annual revenue of 50 and a maximum of 100,
- between 1000 and 30000 employees, or
- have open source in their description

{
    "or": [
        {
            "and": [
                {
                    "revenueMin": {
                        "gte": 50
                    },
                    "revenueMax": {
                        "lte": 100
                    }
                }
            ]
        },
        {
            "employeeCount": {
                "between": [
                    1000,
                    30000
                ]
            }
        },
        {
            "description": {
                "textContains": "open source"
            }
        }
    ]
}