Perform a Right Join Query to Combine Two Datasets

Query

logscale

defineTable(name="users",query={orgId=1},include=[username, name])
| defineTable(name="operations",query={*},include=[username, operation])
| readFile(users)
| match(operations, field=username, strict=false)
| select([username, operation])

Introduction

In this example, the defineTable() function is used as a right join query to extract and combine information from two different datasets.

The event set for the query is in one repository, but the event set for each query is shown separately to identify the two sets of information. The first event set is:

username	name	orgId
user1	John Doe	1
user2	Jane Doe	1
user3	Bob Smith	2

and the other event set:

username	operation
user1	createdFile
user3	createdFile

Step-by-Step

Starting with the source repository events.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1@{ shape: win-pane, label: "Table" } 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
defineTable(name="users",query={orgId=1},include=[username, name])
```
Generates an ad-hoc table named users that has the fields username and name and includes users where orgId field equals 1.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1@{ shape: win-pane, label: "Table" } 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| defineTable(name="operations",query={*},include=[username, operation])
```
Defines a new ad-hoc table that uses all the fields (username and operation) in a table named operations.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1@{ shape: win-pane, label: "Table" } 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| readFile(users)
```
Reads the users ad-hoc table as events using readFile().
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1@{ shape: win-pane, label: "Table" } 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| match(operations, field=username, strict=false)
```
Matches the events that have a matching operation from the operations table with the users table using the username as the common field. Events are not filtered if the events do not match, (implying a right join), by using strict=false
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1@{ shape: win-pane, label: "Table" } 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 4 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| select([username, operation])
```
Selects the username and operation fields to be displayed from the event set.
Event Result set.

Summary and Results

The result will output two events:

username	operation
user1	createdFile
user2	no value

Note that in the event set all operations have been included even when there is no match between the username field, resulting in the no value for user2. If strict=true had been used to the match() function, then the event for user2 would not have been outputted.

Examples Library