join
Compares the fields of data received as input to the subquery result field and joins them.
Note
'join' does not support stream queries. To apply join to stream queries, use the 'streamjoin' command.
Syntax
join [type={cross|full|inner|left|leftonly|right|rightonly}] KEY_FIELD, ... [ SUBQUERY ]
Required Parameter
KEY_FIELD, ...
- Key fields as the criteria for join, separated by a comma (
,
). [ SUBQUERY ]
- Subquery that returns the data to be joined with the input data, enclosed in a pair of square brackets (
[ ]
).
Optional Parameter
type={cross|full|inner|left|leftonly|right|rightonly}
- Join type (default:
inner
).cross
: Also known as "Cartesian product", it returns a set consisting of M x N records by combining a set of input data (M records) with a set of subquery results (N records). This type of JOIN does not require a joining condition.full
: For records with matching keys, it returns by combining them, and for records with no matching keys, it returns them as they are. This is similar to the union of data.- If the keys match, it combines the subquery field with the input data record and then returns.
- If the keys do not match, it returns the input data and the subquery data respectively as they are.
inner
: In general, join refers to "inner join". It combines and returns only the records with matching keys. It does not return records that do not contain keys. This is similar to the intersection of data.left
: For records with matching keys, it returns by combining them, and for records with no matching keys, it returns only records of input data.leftonly
: It returns only records with keys that do not match the set of subquery results. It does not return records with matching keys.right
: For records with matching keys, it returns by combining them, and for records with no matching keys, it returns only the result of the subquery.rightonly
: returns only records with keys that do not match the set of subquery results. It does not return records with matching keys.
Usage
-
Execute
inner
join with the code field as the key.# Pass json with the code field as input data | json "[ {'code':1}, {'code':2}, {'code':3} ]" | # A subquery command that returns json with code and name fields Executes the inner join query using input data and subquery command result data with the "code" field as the key. | join code [ json "[ {'code':1, 'name':'foo'}, {'code':2, 'name':'bar'} ]" ]
Result of inner join:
code name 1 foo 2 bar -
Output the result except those retrieved in the subquery (
leftonly
join).json "[ {'field1': 'A'}, {'field1': 'B'}, {'field1': 'C'}, {'field1': 'D'} ]" | join type=leftonly field1 [ json "[ {'field1': 'A', 'field2': 'Foo'}, {'field1': 'D', 'field2': 'Bar'} ]" ]
Result of leftonly join:
field1 B C The query command executed above has the same execution result as the following query command (application of the result of
left
join).json "[ {'field1': 'A'}, {'field1': 'B'}, {'field1': 'C'}, {'field1': 'D'} ]" | join type=left field1 [ json "[ {'field1': 'A', 'field2': 'Foo'}, {'field1': 'D', 'field2': 'Bar'} ]" ] | search isnull(field2)
-
Output statistics for each department. It returns all departments even if statistical values do not exist (
right
join).json "[ {'id': 1, 'cases': 1000}, {'id':2, 'cases': 2000} ]" | join type=right id [ json "[ {'id':1, 'dept':'sales'}, {'id':2, 'dept':'operation'}, {'id':3, 'dept':'technical'} ]" ]
Result of right join:
id dept cases 1 sales 1000 2 operation 2000 3 technical -
Combine document security violation logs and media control violation logs based on accounts, and output log if they do not match (
full
join).json "[ {'acct':'bob', 'document security violation': 1}, {'acct':'alice', 'document security violation': 5} ]" | join type=full acct [ json "[ {'acct':'alice', 'media control violation': 8}, {'acct':'clark', 'media control violation': 3} ]" ]
Result of full join:
acct document security violation media control violation bob 1 alice 5 8 clark 3