1. Docs
  2. Formulas
  3. Functions

WHERE

Open markdown version

Takes an array of objects, a path and a value (optional). The path can be a single key or a dot-separated series of keys. Returns a new array containing only the objects where the value at the key/path matches the provided value. If value is not provided, it returns an array containing only the objects where the value at the key/path is present.

If you try to pass NULL in for value, it will be treated as not provided, so you cannot use this function to check for NULL values. Instead, you can use FILTER to find NULL values like so:

FILTER(data, LAMBDA(object, object["key"] = NULL)). To return NULL when path has a value of NULL, set the null_on_path_error parameter to TRUE. null_on_path_error is FALSE by default.

Categories: Arrays, Objects

Syntax

WHERE(array, path, [value], [null_on_path_error: FALSE])

Examples

Example 1

Input

1
{
2
"staff": [
3
{
4
"name": "Alice",
5
"department": "engineering"
6
},
7
{
8
"name": "Bob",
9
"department": "sales"
10
},
11
{
12
"name": "Charlie",
13
"department": "engineering"
14
}
15
]
16
}

Formula

WHERE(staff, "department", "engineering")

Output

1
[
2
{
3
"name": "Alice",
4
"department": "engineering"
5
},
6
{
7
"name": "Charlie",
8
"department": "engineering"
9
}
10
]

Example 2

Input

1
{
2
"staff": [
3
{
4
"name": "Alice",
5
"department": {
6
"name": "engineering",
7
"role": "manager"
8
}
9
},
10
{
11
"name": "Bob",
12
"department": {
13
"name": "sales",
14
"role": "assistant"
15
}
16
},
17
{
18
"name": "Charlie",
19
"department": {
20
"name": "sales",
21
"role": "manager"
22
}
23
}
24
]
25
}

Formula

WHERE(staff, "department.role", "manager")

Output

1
[
2
{
3
"name": "Alice",
4
"department": {
5
"name": "engineering",
6
"role": "manager"
7
}
8
},
9
{
10
"name": "Charlie",
11
"department": {
12
"name": "sales",
13
"role": "manager"
14
}
15
}
16
]

Example 3: If 'value' isn't provided, return all objects where the path exists and the value at that path IS_PRESENT.

Input

1
{
2
"staff": [
3
{
4
"name": "Alice",
5
"department": {
6
"name": "engineering",
7
"role": "manager",
8
"team": "AI"
9
}
10
},
11
{
12
"name": "Bob",
13
"department": {
14
"name": "sales",
15
"role": "assistant",
16
"team": " "
17
}
18
},
19
{
20
"name": "Charlie"
21
},
22
{
23
"name": "Denise",
24
"department": {
25
"name": "sales",
26
"role": "manager"
27
}
28
}
29
]
30
}

Formula

WHERE(data.staff, "department.team")

Output

1
[
2
{
3
"name": "Alice",
4
"department": {
5
"name": "engineering",
6
"role": "manager",
7
"team": "AI"
8
}
9
}
10
]

Example 4: If 'path' is provided NULL and 'null_on_path_error' is provided TRUE, return NULL.

Input

1
{
2
"staff": [
3
{
4
"name": "Alice",
5
"department": {
6
"name": "engineering",
7
"role": "manager",
8
"team": "AI"
9
}
10
}
11
]
12
}

Formula

WHERE(data.staff, NULL, null_on_path_error: TRUE)

Output

"NULL"

Sample actions

Event Transform
My Action 2
Event Transform
WHERE
Event Transform
My Action 1

Select an action to inspect

You can also click "Copy actions" and paste them in your Tines story to see how they work.

Was this helpful?