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

  1. 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:

    codename
    1foo
    2bar
  2. 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)
    
  3. 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:

    iddeptcases
    1sales1000
    2operation2000
    3technical
  4. 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:

    acctdocument security violationmedia control violation
    bob1
    alice58
    clark 3