Running join queries by using the wire listener

You can use the wire listener to run join queries on JSON and relational data. The syntax supports collection-to-collection joins, relational-to-relational joins, and collection-to-relational joins. Join queries are supported in sharded environments when parallel sharded queries are enabled.

About this task

Join queries in the wire listener are done by submitting a join query document to the system.join pseudo table.

Wire listener join queries support the sort, limit, skip, and explain options that you can set on a MongoDB cursor.
Fields that are specified in the sort clause must also be included in the projection clause.
The $hint operator is not supported.

Procedure

Create a join query document.
The join query document has the following syntax:
$collections

This required HCL OneDB™ JSON operator defines the two or more collections or relational tables that are included in the join.

$project

This required MongoDB JSON operator applies a projection clause to the table_or_collection_name that is specified.

$where

This optional MongoDB JSON operator applies a query filter to the table or relational table. You can use any of the supported query operators that are listed here: Query and projection operators.

$condition

This required HCL OneDB JSON operator defines how the specified collections or tables are joined. You can specify a condition by mapping a single table column to another single table column, or a single table column to multiple other table columns.
Run a find query against a pseudo table that is named system.join with the join query document specified.
For example, in the MongoDB shell:
```
> db.system.join.find({join_query_document})
```

Results

The query results are returned.

Examples of join query document syntax

This example retrieves customer orders that total more than $100. The join query document joins the customer and orders tables, on the customer_num field where the order total is greater than 100. The same query document works if the customers and orders tables are collections, relational tables, or a combination of the two.

{"$collections":
        {
             "customers":
                 {"$project":{customer_num:1,name:1,phone:1}},
             "orders": 
                 {"$project":{order_num:1,nitems:1,total:1,_id:0},
                  "$where":{total:{"$gt":100}}}
        },
  "$condition":
        {"customers.customer_num":"orders.customer_num"}
}

This example retrieves the order, shipping, and payment information for order number 1093. The array syntax is used in the $condition syntax of the join query document.

{"$collections":
         {
             "orders":
                  {"$project":{order_num: 1,nitems: 1,total: 1,_id:0},
                   "$where":{order_num:1093}},
             "shipments":
                  {"$project":{shipment_date:1,arrival_date:1}},
             "payments": 
                  {"$project":{payment_method:1,payment_date:1}}
         },
  "$condition":
    {"orders.order_num":["shipments.order_num","payments.order_num"]}
}

This example retrieves the order and customer information for orders that total more than $1000 and that are shipped to the postal code 10112.

{"$collections":
        {
            "orders": 
                 {"$project":{order_num:1,nitems:1,total:1,_id:0},
                  "$where":{total:{$gt:1000}}},
            "shipments":
                 {"$project":{shipment_date:1,arrival_date:1,_id:0},
                  "$where":{address.zipcode:10112},
            "customer":
                 {"$project":{customer_num:1,name:1,company:1,_id:0}}
       },
  "$condition":
       {
            "orders.order_num":"shipments.order_num",
         "orders.customer_num":"customer.customer_num",
       } 
}