---
title: "Complex data types"
canonical: "https://docs.devo.com/space/latest/978583650/Complex%20data%20types"
format: markdown
---
> Macro (toc)
## About complex types
In Devo, complex data types provide flexible and powerful structures for querying and manipulatingdata logs. These types include **set**, **map**, **array**, and **tuple**, each with unique properties allowing users to efficiently handle diverse data operations. This article outlines the key characteristics and operations associated with each data type.
| Type | Description | Example |
| --- | --- | --- |
| **[Set](https://devodocs.atlassian.net/wiki/spaces/latest/pages/978583650/Complex+data+types#Set)** | Unordered, unique collection of values | `{1, 2, 3}` |
| **[Map](https://devodocs.atlassian.net/wiki/spaces/latest/pages/978583650/Complex+data+types#Map)** | Key-value pairs, unique keys | `{"a": 1, "b": 2}` |
| **[Array](https://devodocs.atlassian.net/wiki/spaces/latest/pages/978583650/Complex+data+types#Array)** | Ordered collection, allows duplicates | `["item1", "item2", "item3"]` |
| **[Tuple](https://devodocs.atlassian.net/wiki/spaces/latest/pages/978583650/Complex+data+types#Tuple)** | Ordered, immutable collection, mixed types | `(1.33, "text", true)` |
| **[JSON](https://devodocs.atlassian.net/wiki/spaces/latest/pages/978583650/Complex+data+types#JSON)** | Key-value pairs, unordered, unique keys, mixed types | `{"a": "b", "c": 1, "d" : [1,2,3], "e": {"f": 1}}` |
These complex data types provide the flexibility for handling structured and unstructured data in Devo, enabling efficient queries and operations across vast datasets. Users can effectively manipulate and analyze data within the Devo environment by leveraging sets, maps, arrays, and tuples.
> ℹ️ The JSON type is not considered a complex type but it is included in this documentation for similarity purposes. Currently, it is not possible to cast JSON natively from/to a complex type.
---
## **Set**
A **set** in Devo represents an unordered collection of **unique values**, meaning no duplicates are allowed. The order of elements in a set is not guaranteed, making it ideal for operations where uniqueness is more important than sequence.
**Example:**
```
{1, 2, 3}
```
#### **Key Characteristics:**
- **Unordered**: Elements do not follow a specific order.
- **Unique**: No duplicates are allowed.
#### Related operations:
| Operation | Description | Syntax | Example | Result Value | Result Type |
| --- | --- | --- | --- | --- | --- |
| **[mkset or {}](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389152782)** | creates a set from elements | mkset (ele_1, ... ,ele_n)
{ele_1, ... , ele_n} | mkset (1.33,2,3,4)
mkset ("One", "two", "Number 3")
mkset (domain, responseTime)
mkset (responseTime, srcPort) | {1.33, 2, 3, 4}
{One, two, Number 3}
{One, 77}
{demo, 12}
{12, 37406} | set(float)
set(str)
set(str)
set(str)
set(int) |
| **[isempty](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201054)** | Checks if a set is empty | isempty (set) | isempty ({1, 2, 3})
isempty ({ }) | false
true | bool |
| **[length](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201158)** | Returns the length of a set | length (set) | length ({1, 20, 3, 4, 20, 20}) | 4 | int |
| **[has (->)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95200521)** | Checks the occurrence of
a specified value in a
given set | has (set, ele)
set -> ele | has ({31, 2, 77}, 77)
{31, 2, 77} -> 77 | true
true | bool |
| **[in (<-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201087)** | Checks the occurrence of
a specified value in a
given set | `in` (ele, set)
ele <- set | `in` (77, {30, 2, 77})
77 <- {30, 2, 77} | true
true | bool |
| **[has (->)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95200521)** | Checks whether a set
contains another set | has (set_1, set_2)
set_1 -> set_2 | has ({1, 2 , 3, 4}, {2, 1})
{1, 2 , 3, 4} -> {2, 1} | true
true | bool |
| **[in (<-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201087)** | Checks whether a set
contains another set | `in` (set_1, set_2)
set_1 <- set_2 | `in` ({1, 2 , 3, 4}, {2, 1})
{1, 2 , 3, 4} <- {2, 1} | false
false | bool |
| **[add (+)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193212)** | Adds some value to a set | add (set, ele)
add (ele, set) | add ({30, "Peter", 77}, “Ann")
{30, "Peter", 77} + “Ann" | {30, Peter, 77, Ann}
{30, Peter, 77, Ann} | set(str)
set(str) |
| **[add (+)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193212)** | Union of two sets | add (set_1, set_2)
set_1 + set_2 | add ({5, "Peter", 77}, {77, "Ann", 400})
{5, "Peter", 77} + {77, "Ann", 400} | {5, Peter, 77, Ann, 400]
{5, Peter, 77, Ann, 400] | set(str)
set(str) |
| **[join *](https://devodocs.atlassian.net/wiki/spaces/latest/pages/388923425)** | Joins the strings of a given
set in a single string
(separataror can be added)
NOTE : no order granted | join (set_str)
join (set_str, sep) | join ({1, "Peter", 77, 77, "Ann"})
join ({1, "Peter", 77, 77, "Ann"} , "_x_") | 1Ann77Peter
1_x_Ann_x_77_x_Peter | str |
| **[band](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198338)** | Intersection of two sets | band (set_1, set_2)
set_1 & set_2 | band ({30, 2, 77}, {77, 400})
band ({30, 2, 77}, {500, 400})
{30, 2, 77} & {77, 400}
{30, 2, 77} & {500, 400} | {77}
{}
{77}
{} | set(int)
set(int)
set(int)
set(int) |
| **[sub (-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193656)** | Deletes a value from a set | sub (set, ele)
set - ele | sub ({30, 2, 77}, 400)
{30, 2, 77} - 400
sub ({30, "Peter", 77}, "Peter")
{30, "Peter", 77} - "Peter" | {30, 2, 77}
{30, 2, 77}
{30, 77}
{30, 77} | •set(int)
•set(int)
•set(str)
•set(str) |
| **[sub (-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193656)** | Difference of two sets | sub (set_1, set_2)
set_1 – set_2 | sub ({30, 2, 77}, {400, 30})
{30, 2, 77} - 400
sub ({30, "Peter", 77}, {"Peter", 30})
{30, "Peter", 77} - {"Peter", 30} | {2, 77}
{2, 77}
{77}
{77} | set(int)
set(int)
set(str)
set(str) |
### **Common Operations:**
```
from siem.logtrust.web.activity
```
- **Create a set** from values:
- **Convert an array to a set**:
- **Check if a set contains a specific value**:
- **Add values to a set**:
- **Get distinct values in a set out of a grouped field**
- **Get distinct values in a set out of a grouped field**
### Test them together in Data Search
```sql
from siem.logtrust.web.activity
//create a set
select mkset(srcHost,params) as mkset_string
select {srcHost, params}
//convert an into a set
select [1,2,3] as array,
set(array) as toset_int,
//check if the set is empty
isempty(mkset_string) as isemty,
//what is the length of the array
length(mkset_string) as length,
//does the set contain a specific item?
toset_int -> 2 as has,
//add values to a set
"new value" + mkset_string as add_va,
//join a set. Concatenates all the values of a set
join(mkset_string, ",") as join_set
//Get distinct values in a set out of a grouped field
//group select collectdistinct(responseLength) as responseLength_sizedistinct
```
---
## **Map**
A **map** in Devo is a collection of **key-value pairs** where each key is unique, but values can be duplicated. Maps allow for efficient lookups by keys and are useful when working with datasets that need to associate properties or attributes to specific items.
**Example:**
```
{"src": srcPort, "serverPort": serverPort}
```
#### **Key Characteristics:**
- **Key-Value Structure**: Each pair associates a key with a value.
- **Unique Keys**: Each key must be unique, though values can repeat.
#### Related operations:
| Operation | Description | Syntax | Example | Result Value | Result Type |
| --- | --- | --- | --- | --- | --- |
| **[mkmap or { : }](https://devodocs.atlassian.net/wiki/spaces/latest/pages/388169736)** | Creates a map from
keys and values | mkmap (key_1, val_1, ... , key_n, val_n) | mkmap (10, 1001, 20, 1.33)
mkmap (10, 1001, 20, "Yesterday")
mkmap ("One", 100, "Two", 200)
{ 1 : "Hello", 2 : "Bye" } | {10:1001, 20:1.33}
{10=1001, 20=Yesterday}
{One=100, Two=200}
{1=Hello, 2=Bye} | map(int, float)
map(int, str)
map(str, int)
map(int, str) |
| **[collectdistinct()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/446234632)**** (aggr)** | Creates a map of distinct values after an aggregation | collectdistinct(aggr_field) | ```
from siem.logtrust.web.activity
select {1, 20, 3, 4, 20, 20} as map
group
select collectdistinct(map)
``` | {{1, 3, 20, 4}} | map(int, int,int,int) |
| **[isempty](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201054)** | Checks if a map is empty | isempty (map) | isempty ({1:10, 2:20, 3:30})
isempty ({}) | false
true | bool |
| **[length](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201158)** | Returns the length of a
map | length (map) | length ({1:"a", 2:"b", 3:"c"}) | 3 | int |
| **[has (->)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95200521)** | Checks the
occurrence of a
specified key in a
given map | has (map, key)
map -> key | has ({1:"aaa", 2:"bbb", 77:"ccc"}, 77)
{1:"aaa", 2:"bbb", 77:"ccc"} -> 77 | true
true | bool |
| **[in (<-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201087)** | Checks the
occurrence of a
specified key in a
given map | `in` (key, map)
key <- map | `in` (77, {1:"aaa", 2:"bbb", 77:"ccc"})
77 <- {1:"aaa", 2:"bbb", 77:"ccc"} | true
true | bool |
| **[add (+)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193212)** | Adds some key-value
pair to a map | add (map, key, value) | add ({5:“Peter", 10:“Ann"}, 4, “xxx") | {5=Peter, 10=Ann, 4=xxx} | map(int, str) |
| **[add (+)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193212)** | Concatenation of two
maps | add (map_1, map_2)
map_1 + map_2 | add ({5:"Peter", 10:"Ann"}, {4:"xxx"})
{5:"Peter", 10:"Ann"} + {4:"xxx"} | {5=Peter, 10=Ann, 4=xxx}
{5=Peter, 10=Ann, 4=xxx} | map(int, str)
map(int, str) |
| **[at [ ]](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389152771)** | Given a map, returns the
value associated to a
given key | at (map, key)
map [key] | at ({"Hello":77, 4:4000}, "Hello")
{"Hello":77, 4:4000} ["Hello"]
at ({1:"One", 2:"Two"}, 888)
{1:"One", 2:"Two"} [888] | 77
77
null
null | int
int
str
str |
| **[band](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198338)** | Intersection of two maps :
•Non-commutative
•Intersection by key
•Returns first map value | band (map_1, map_2)
map_1 & map_2 | band ({5:"Five", 2:"Two"}, {2:"xxx"})
{5:"Five", 2:"Two"} & {2:"xxx"}
band ({5:"Five", 2:"Two"}, {6:“Six"})
{5:"Five", 2:"Two"} & {6:“Six"} | {2=Two}
{2=Two}
{}
{} | map(int, str)
map(int, str)
map(int, str)
map(int, str) |
| **[band](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198338)** | Restrict a map to a set of
keys | band (map, set)
map & set | band ({5:"Five", 2:"Two", 6:"Six"}, {6, 2})
{5:"Five", 2:"Two", 6:"Six"} & {6, 2}
band ({5:"Five", 2:"Two"}, {10, 20})
{5:"Five", 2:"Two"} & {10, 20} | {2=Two, 6=Six}
{2=Two, 6=Six}
{}
{} | map(int, str)
map(int, str)
map(int, str)
map(int, str) |
| **[sub (-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193656)** | Deletes the key-value pair
defined by a key | sub (map, key)
map - key | sub ({5:"Five", 2:"Two", 6:"Six"}, 5)
{5:"Five", 2:"Two", 6:"Six"} - 5} | {2:“Two", 6:“Six"}
{2:“Two", 6:“Six"} | map(int, str)
map(int, str) |
| **[sub (-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193656)** | Deletes the key-value pairs
defined by a set of keys | sub (map, set)
map – set | sub ({5:"Five", 2:"Two", 6:"Six"}, {2, 5, 10})
{5:"Five", 2:"Two", 6:"Six"} - {2, 5, 10} | {6=Six}
{6=Six} | map(int, str)
map(int, str) |
| **[sub (-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193656)** | Difference of two maps
(difference by key) | sub (map_1, map_2)
map_2 – map_2 | sub ({5:"Five", 2:"Two", 6:"Six"} {5:“xxx"})
{5:"Five", 2:"Two", 6:"Six"}- {5:“xxx"} | {2:“Two", 6:“Six"}
{2:“Two", 6:“Six"} | map(int, str)
map(int, str) |
| **[keys](https://devodocs.atlassian.net/wiki/spaces/latest/pages/388825112)** | Returns the set of keys of a given map | keys (map) | keys ({"Hello":77, 4:4000})
keys ({99:"Monday", 4:4000}) | {Hello, 4}
{99, 4} | set(str)
set(int) |
| **[values](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389251087)** | Returns the array of values of a given map
NOTE : no order granted | values (map) | values ({"Hello":77, 4:4000})
values ({99:"Monday", 4:4000}) | [77, 4000]
[4000, Monday] | array(int)
array(str) |
### **Common Operations:**
- **Create a map**:
- **Check if a map is empty**:
- **Get the length of a map** (number of key-value pairs):
- **Access a value by key**:
- **Append new key-value pairs**:
- **Subtract pairs**
- **Return the value of a give key**
- **Return all keys or values** from a map:
### Test them together in Data Search
```sql
from siem.logtrust.web.activity
//create a map
select mkmap("b",7,"c",6,"a",5) as map1
select {"src":srcPort, "serverPort": serverPort} as map2
//Checks if a map is empty
select isempty(map1) as _false
//Returns the length of a map
select length(map1) as _length
//check the occurrence of key "b"?
select map1 -> "b" as _true
//append of new pairs
select map1 + map2 as map3
//subtract pairs
select map3 - map2 as _substract_pairs
select map3 - "b" as _substract_key_b
//return the value of a give key
select map3["b"] as _return_7
//return all the keys or values of a map
select keys(map3) as _keys_set
select values(map3) as _values_array
```
---
## **Array**
An **array** is an ordered collection of elements where duplicates are allowed. Arrays are especially useful for performing a range of operations such as sorting, summing, and filtering. Unlike sets, arrays preserve the order of elements, making them essential for ordered data manipulation.
**Example:**
```
["usernameJohn", "usernameMary", "usernameJoe", "usernameJane"]
```
#### **Key Characteristics:**
- **Ordered**: Elements follow a specific sequence.
- **Allows Duplicates**: Identical elements can appear multiple times.
#### Related operations:
| Operation | Description | Syntax | Example | Result Value | Result Type |
| --- | --- | --- | --- | --- | --- |
| **[mkarray or []](https://devodocs.atlassian.net/wiki/spaces/latest/pages/386891796)** | Creates an array from elements | mkarray (ele_1, … ,ele_n)
[ele_1, … , ele_n] | mkarray (1.33,2,3,4) mkarray (“One", "two", "Number 3“)
[ “One", 77 ]
mkarray (domain, responseTime) | [1.33, 2, 3, 4]
[One, two, Number 3]
[One, 77] [demo, 12] | array(float) array(str) array(str) array(str) |
| **[split](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201437)** | Creates an array by splitting a string field | split (string, string) | `split("1.2.3.4", ".")` | [1, 2, 3, 4] | array |
| **[isempty](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201054)** | Checks if an array is empty | isempty (array) | isempty ([1, 2, 3]) isempty ([ ]) | false true | bool |
| **[length](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201158)** | Returns the length of an array | length (array) | length ([1, 2, 3, 4, 5, 6]) | 6 | Int |
| **[has (->)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95200521)** | Checks the occurrence of a specified value in a given array | •has (array, ele) •array -> ele | has ([30, 2, 77], 77)
[30, 2, 77] -> 77 | true
true | bool |
| **[in (<-)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201087)** | Checks the occurrence of a specified value in a given array | `in` (ele, array) ele <- array | `in` (77, [30, 2, 77])
77 <- [30, 2, 77] | true
true | bool |
| **[add (+)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193212)** | Appends some value to the end of an array | add (array, ele)
array + ele | add ([30, "Peter", 77], "Ann")
[30, "Peter", 77] + "Ann" | [30, Peter, 77, Ann]
[30, Peter, 77, Ann] | array(str)
array(str) |
| **[add (+)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193212)** | Concatenates two arrays | add (array_1, array_2)
array_1 + array_2 | add ([5, "Peter", 77], [77, "Ann", 400])
[5, "Peter", 77] + [77, "Ann", 400] | [5, Peter, 77, 77, Ann, 400]
[5, Peter, 77, 77, Ann, 400] | array(str)
array(str) |
| **[join](https://devodocs.atlassian.net/wiki/spaces/latest/pages/388923425)** | Joins the strings of a given
array in a single string
(separataror can be added) | join (array_str)
join (array_str, sep) | join ([1, "Peter", 77, 77, "Ann"])
join ([1, "Peter", 77, 77, "Ann"], “_x_”) | 1Peter7777Ann
1_x_Peter_x_77_x_77_x_Ann | str |
| **[at or [][]](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389152771)** | Returns the n-th element of
an array | at (array, n)
array [n] | at ([10, 20, 3.33, 40], 1)
[10, 20, 3.33, 40] [2]
at ([10, 20, 3.33, "HELLO"], 3)
at ([10, 20, 333, 40], 777) | 20
3.33
HELLO
null | float
float
str
int |
| **[at or [][]](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389152771)** | Returns a subarray between
two indices | at (array, from, to)
array [from, to] | at ([10, 20, 30, 40], 1, 3)
["A", "B", "C", "D"] [1, 3] | [20, 30]
["B", "C"] | array(int)
array(str) |
| **[indexof](https://devodocs.atlassian.net/wiki/spaces/latest/pages/388890626)** | Returns the index of the first
appearance of a value in
an array (-1 if not present)
starting at 0 | indexof (array, ele) | indexof ([10, 20, 3.33, 40], 3.33)
indexof ([10, 20, 333, 40], 777)
indexof (["Hello", "my friend"],"Hello") | 2
-1
0 | int |
| **[dropnulls](https://devodocs.atlassian.net/wiki/spaces/latest/pages/388923414)** | Drops null elements of a given array | dropnulls (array) | dropnulls ([3, null, 77, null])
dropnulls ([3, null, "HELLO", null]) | [3, 77]
[3, HELLO] | array(int)
array(str) |
| **[sort](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389120003)** | Sorts the elements of a
given array | sort (array) | sort ([3, 77, 30, 1)
sort (["AAA", "aaa", "44", 5]) | [1, 3, 30, 77]
[44, 5, AAA, aaa] | array(int)
array(str) |
| **[reverse](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95201362)** | Reverses the elements of
a given array | reverse
(array) | reverse ([3, 77, 30, 1)
reverse (["AAA", "aaa", "44", 5]) | [1, 30, 77, 3]
[5, 44, aaa, AAA] | array(int)
array(str) |
| **[sum](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95193032)** | Sums the values of a
numeric array | sum (array) | sum([1,2,3,4])
sum([1,2,3.5,4.5]) | 10
11 | Int
float |
### **Common Operations:**
- **Create an array**:
- **Convert a set to an array**:
- **Check if an array contains a value**:
- **Add a value to an array**:
- **Reverse an array**:
- **Drop null values** from an array:
- **Sum numeric arrays**:
- **length of an array**
### Test them together in Data Search
```sql
from siem.logtrust.web.activity
//create an array
select mkarray(username, ip4(srcHost), mm2coordinates(ip4(srcHost)), true) as array1
select ["hi", "i_am_in_a_set"] as array2
//convert a set to an array
select {serverPort, srcPort} as set1
select array(set1) as array3
//filter or check the occurrance of a value in an array
select "hi" in array2
select array2 -> "hi"
//length of an array
select length(array2)
//add a value
select array2 + "example" as array_with_example
//drop nulls
select dropnulls(array2) as array_without_nulls
//reverse an array
select reverse(array2) as array_reversed
select reverse("hello") as _treat_strings_as_arrays
//sum numeric arrays
select sum([1,5,8]) as _14
```
---
## **Tuple**
A **tuple** in Devo is an ordered collection of elements that can be of mixed types (e.g., strings, numbers, booleans). Unlike arrays, tuples maintain the individual types of each element and are **immutable**, meaning that once created, the elements cannot be changed.
**Example:**
```
(1.33, "text", true)
```
#### **Key Characteristics:**
- **Ordered**: Elements are stored in a specific sequence.
- **Immutable**: The values in a tuple cannot be modified after creation.
- **Allows Duplicates**: Identical elements can appear more than once.
- **Allows Mixed Types**: Elements can be of any type (e.g., string, integer, boolean).
#### Related operations:
| **Operation** | **Description** | **Syntax** | **Example** | **Result Value** | **Result Type** |
| --- | --- | --- | --- | --- | --- |
| **[mktuple](https://devodocs.atlassian.net/wiki/spaces/latest/pages/617119745)** | Creates a tuple with specified elements. | `mktuple(elem_1, elem_2, ..., elem_n)` | `mktuple(username, ip4(srcHost), mm2coordinates(ip4(srcHost)), true)` | `(username, ip4, coordinates, true)` | `tuple(varied types)` |
| **Tuple literal** | Creates a tuple using parentheses. | `(elem_1, elem_2, ..., elem_n)` | `(username, srcPort, ip4(srcHost), true)` | `(username, srcPort, ip4, true)` | `tuple(varied types)` |
| **Access by index** | Access tuple elements using index. Supports negative indexing. | `tuple[index]` | `tuple[0]`
`tuple[-1]` | First element
Last element | Matches element type |
| **[at()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389152771)** | Access tuple elements by index using a function. | `at(tuple, index)` | `at(tuple, 0)` | First element | Matches element type |
| **Filter by type** | Filters tuple elements based on data type or pattern. | `where tuple[index] -> type` | `where tuple[0] -> "@"` | Matches filtered criteria | `bool` |
| **Filter by value** | Filters tuple elements based on value comparison. | `where tuple[index] in/not in (values)` | `where tuple[-1] not in (ip4(95.63.39.51))` | Matches filtered criteria | `bool` |
| **Sub-query (key lookup)** | Finds the occurrence of a key in another dataset during the same period. | `(from dataset group by key select key) -> key` | `(from siem.logtrust.web.navigation group by srcHost select srcHost) -> srcHost` | Matched key occurrence | Matches key type |
| **Sub-query (field lookup)** | Returns a field from another table matching a condition. | `(from dataset group by field1, field2)[key]` | `(from siem.logtrust.web.navigation group by userEmail, origin)[username]` | `origin` field value | Matches field type |
| **Sub-query (tuple matching)** | Returns a tuple from another table matching specified conditions. | `(from dataset group by tuple_field select fields)[key1, key2]` | `(from siem.logtrust.web.navigation group by userEmail, level select userEmail, count())[username, level]` | `(userEmail, count)` | `tuple(varied types)` |
### **Common Operations:**
- **Create a tuple**:
- **Access tuple elements by index**:
- **Filter tuples by type**:
- **SUB-QUERY: find the occurrence of a specific IP in another table during the same period of time**
- **SUB-QUERY: return the "origin" field in another table matching by user email**
- **SUB-QUERY: return the tuple (userEmail, count()) from another table matching by the tuple (email, level)**
### Test them together in Data Search
```sql
from siem.logtrust.web.activity
//create a tuple with multiple types
select mktuple(username, ip4(srcHost), mm2coordinates(ip4(srcHost)), true) as tuple
select (username ,srcPort, ip4(srcHost), true) as tuple2
//some ways to select the first item from a tuple
select tuple[0] as first_item_from_tuple
select tuple[-1] as last_item_from_tuple_
select at(tuple,0) as first_item_from_tuple2
//SUB-QUERY: find the occurrence of a specific IP in another table during the same period of time
select (from siem.logtrust.web.navigation group by srcHost select srcHost) -> srcHost as _ip_occurrence_in_another_table
//SUB-QUERY: return the "origin" field in another table matching by user email
select (from siem.logtrust.web.navigation group by userEmail, origin)[username] as userInSubq
//SUB-QUERY: return the tuple (userEmail, count()) from another table matching by the tuple (email, level)
select (from siem.logtrust.web.navigation group by userEmail, level select userEmail, count())[username, level] as match
//it is possible to filter each item by the underlying data type
where tuple[0] -> "@"
where tuple[-1] not in (ip4(95.63.39.51))
```
---
## **JSON**
Then JSON format is fully supported in Devo. It is useful for representing structured data in an unordered format, enabling flexible data storage and manipulation.
- In Data Search: you can use the "pretty-print" view to inspect the content of JSON cells and extract the values that you need. [How?](https://docs.devo.com/space/latest/95191311/Working+with+JSON+objects+in+data+tables#Check-JSON-content-using-the-pretty-print-view)
**Example:**
```
{
"a": "b",
"c": 1,
"d": [1, 2, 3],
"e": {
"f": 1
}
}
```
#### Key Characteristics:
- **Unordered**: The sequence of keys is not guaranteed.
- **Unique Keys**: Each key must be unique within the object.
- **Mixed Types**: Values can be strings, numbers, booleans, arrays, or other JSON objects.
- **Flexible**: Supports nested objects and arrays for complex structures.
| **Operation** | **Description** | **Syntax** | **Example** | **Result** | **Result Type** |
| --- | --- | --- | --- | --- | --- |
| **[jqeval()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198042)** | Extracts values from JSON objects using a `jq` filter. | `jqeval(jq_filter, json_field)` | `jqeval(jqcompile(".email"), json)` | Extracted value from JSON | Matches field type |
| **[jqcompile()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198077)** | Compiles a string as a `jq` filter to be used in `jqeval`. | `jqcompile(filter)` | `jqcompile(".email")` | A compiled `jq` filter | `jq` filter |
| **[label()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95197976)** | Returns the data type of a value inside a JSON object. | `label(json_field)` | `label(json.email)` | `"string"` | `string` |
| **[jsonparse()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198015)** | Converts a string field to a JSON data type. | `jsonparse(string_field)` | `jsonparse(data_string)` | JSON object | `json` |
| **[str()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95194741)** | Converts a JSON object or other data type to a string. | `str(value)` | `str(jqeval(jqcompile(".email"), json))` | Extracted value as a string | `string` |
| **[at() []](https://devodocs.atlassian.net/wiki/spaces/latest/pages/389152771)** | Given a map, returns the
value associated to a
given key | at (json, key)
json [key] | `json["int"]`
`at(json, "int")` | Extract a value from a key | Matches field type |
| **[stringify()](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95194793)** | Converts a JSON object (*json* data type) into* string *data type. | stringify(json_type) | stringify(json_type) | {“a“: 1, “b“: ”c”} | `string` |
### **Common Operations:**
- **Parse String to JSON**
- **Extract a Property Value**
- **Convert a JSON to a string**
- **Determine Data Type of a JSON Value**
### Test them together in Data Search
```sql
from siem.logtrust.web.activity
//create a JSON object
select jsonparse("{\"str\": \"hi\", \"int\": 1}") as json
//Extract a Property Value
//select json["int"] as extract_int //doesn't work in Data Search at the moment
select jqeval(jqcompile(".int"), json) as extract_int_alt
//Convert a JSON to a string
select stringify(json) as json_to_strinc
//Determine Data Type of a JSON Value
select label(jqeval(jqcompile(".int"), json)) as int_type
```