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 in the REST listener are performed by running GET request against the system.join pseudo table with a join query document supplies in the query parameter.

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.

Join queries are supported in sharded environments when parallel sharded queries are enabled.

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 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 GET request against the pseudo table that is named system.join with the join query document specified as the query parameter named query. For example, GET /mydb/system.join?query={join_query_document}

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",
       } 
}