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 ": { "table_or_collection_name" :{"$project ":{specifications } [ ,"$where ":{filter} ] } , "$condition": { { "tabName1.column" :"tabName2.column" | "tabName1.column" :[ "tabName2.column" ] } } }
- $collections
- This required Informix® 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 Informix® 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
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",
}
}