join
| This command is experimental |
Examples
The basic join syntax looks like this:
%dpl
// first option
index=dataset | join <table column name> [ <subsearch> ]
// second option
index=dataset1 | join <dataset-type>:dataset2You can either use sub search or dataset-type with join but not both at the same time.
Sub Search
Sub search is a secondary search done with the join command. It defines the source of results you want to join to.
Use search command to do the sub search. The sub search must be enclosed in square brackets and results shouldn’t exceed the available memory.
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join user [search index=join_json_three earliest=2021-01-01T00:00:00.000+03:00 | spath]
| table _time, user, operation, target, success, count, R_operation, R_target, R_success, R_count
Dataset-Type
Options for dataset-type argument are:
-
datamodel -
savedsearch -
inputlookup
Use the argument when you have created a dataset with datamodel, savedsearch or inputlookup transform commands.
|
|
Type
type argument defines what type of join the join command is going to use.
Options for type argument are:
-
inner -
left -
outer
The default value for the type argument is inner.
type argument uses the same logic as in SQL joins. The below picture illustrates differences between type join options.
Inner Join
inner join includes only results that match with the main search and sub search.
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join type=inner user operation success [search index=join_json_three earliest=2021-01-01T00:00:00.000+03:00 | spath]
| table _time, user, operation, success, target, count, R_target, R_count
Left Join
left join includes results from the main search and results that match with the main search from the sub search.
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join type=left user [search index=join_json_three earliest=2021-01-01T00:00:00.000+03:00 | spath]
| table _time, user, operation, target, success, count, R_operation, R_target, R_success, R_count
Outer Join
outer join includes all results from both datasets, even those where the sub search don’t match with the main search.
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join type=outer user [search index=join_json_three earliest=2021-01-01T00:00:00.000+03:00 | spath]|
Currently, outer join in DPL doesn’t work. This will be fixed before the community release. |
Overwrite
overwrite argument overwrites same table column names from the main search. The argument takes only boolean values. By default, the value is true.
|
Currently, the default value for |
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join overwrite=true user [search index=json_join_three earliest=2021-01-01T00:00:00.000+03:00 | spath]
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join overwrite=false user [search index=json_join_three earliest=2021-01-01T00:00:00.000+03:00 | spath]
Max
max argument defines the maximum amount of sub search results that can be joined with the main search results.
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join max=6 user [search index=join_json_three earliest=2021-01-01T00:00:00.000+03:00 | spath]If the value is set to 0, all results will be shown.
%dpl
index=join_json_one earliest=2021-01-01T00:00:00.000+03:00
| spath
| join max=0 user [search index=join_json_three earliest=2021-01-01T00:00:00.000+03:00 | spath]|
Currently, join’s |