Running join queries through REST
You can use the REST API to run join queries on JSON and relational data. This join syntax supports collection-to-collection joins, relational-to-relational joins, and collection-to-relational joins.
About this task
- Join queries also support the sort, limit, skip, and explain query parameters.
- Fields that are specified in the sort clause must also be included in the projection clause.
Procedure
Example 1
This example retrieves the 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 would be used if the customers and orders tables are collections,
relational tables, or a combination of the
two.
GET /mystore/system.join?query=
{"$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.
GET /stores_demo/system.join?query=
{"$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.
GET /stores_demo/system.join?query=
{"$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",
}
}