MyCodeIdeas: JSON

Nested queries help you to use the result of one query as an input parameter of another, and the queries executed in a nested query works in a transactional manner.

You can use the default h2 database tables comes with the WSO2 DSS to run these samples.

Nested Query Sample with XML Output Mapping

<data name="TestNested" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin</sql>
      <result element="Customer">
         <element column="CUSTOMERNAME" name="Name"/>
      </result>
      <param name="customerNumberin" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER from ORDERS o</sql>
      <result element="Entries" rowName="Entry">
         <element column="ORDERNUMBER" name="ORDERNUMBER" xsdType="string"/>
         <element column="ORDERDATE" name="ORDERDATE" xsdType="string"/>
         <element column="STATUS" name="STATUS" xsdType="string"/>
         <element column="CUSTOMERNUMBER" name="CUSTOMERNUMBER" xsdType="string"/>
         <call-query href="CustomerNameSQL" requiredRoles="">
            <with-param name="customerNumberin" query-param="customerNumber"/>
         </call-query>
      </result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>
http://172.17.0.1:9763/services/TestNested.HTTPEndpoint/orderlist

Request:

http://172.17.0.1:9763/services/TestNested.HTTPEndpoint/orderlist

Sample Response:

<Entries xmlns="http://ws.wso2.org/dataservice">
   <Entry>
      <ORDERNUMBER>10100</ORDERNUMBER>
      <ORDERDATE>2003-01-06+06:00</ORDERDATE>
      <STATUS>Shipped</STATUS>
      <CUSTOMERNUMBER>363</CUSTOMERNUMBER>
      <Customer>
         <Name>Online Diecast Creations Co.</Name>
      </Customer>
   </Entry>
   <Entry>
      <ORDERNUMBER>10101</ORDERNUMBER>
      <ORDERDATE>2003-01-09+06:00</ORDERDATE>
      <STATUS>Shipped</STATUS>
      <CUSTOMERNUMBER>128</CUSTOMERNUMBER>
      <Customer>
         <Name>Blauer See Auto, Co.</Name>
      </Customer>
   </Entry>
</Entries>

Nested Query Sample with JSON output Mapping

Same above service but with json output mapping is as follows.
The format of the mapping should be as follows.
"@target_query_name": "$nested_query_parameter->outer_Query_result_element_name"

<data name="TestNestedJSON" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin</sql>
      <result outputType="json">{"Customers": {"Customer": [{"Name": "$CUSTOMERNAME"}]}}</result>
      <param name="customerNumberin" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER from ORDERS o</sql>
      <result outputType="json">{"Entries": {"Entry": [{"ORDERNUMBER": "$ORDERNUMBER","ORDERDATE": "$ORDERDATE","STATUS": "$STATUS","CUSTOMERNUMBER": "$CUSTOMERNUMBER","@CustomerNameSQL": "$customerNumberin-&gt;customerNumber"}]}}</result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>

Request:

http://172.17.0.1:9763/services/TestNestedJSON.HTTPEndpoint/orderlist

Response:

with header Accept: applicaton/json

{
    "Entries": {
        "Entry": [{
            "Customers": {
                "Customer": [{
                    "Name": "Online Diecast Creations Co."
                }]
            },
            "ORDERNUMBER": "10100",
            "CUSTOMERNUMBER": "363",
            "STATUS": "Shipped",
            "ORDERDATE": "2003-01-06+06:00"
        }, {
            "Customers": {
                "Customer": [{
                    "Name": "Blauer See Auto, Co."
                }]
            },
            "ORDERNUMBER": "10101",
            "CUSTOMERNUMBER": "128",
            "STATUS": "Shipped",
            "ORDERDATE": "2003-01-09+06:00"
        }]
    }
}

Without Accept:application/json header

<Entries xmlns="http://ws.wso2.org/dataservice/CustomerOrderSQL">
  <Entry>
    <Customers xmlns="http://ws.wso2.org/dataservice/CustomerNameSQL">
      <Customer>
        <Name>Online Diecast Creations Co.</Name>
      </Customer>
    </Customers>
    <ORDERNUMBER>10100</ORDERNUMBER>
    <CUSTOMERNUMBER>363</CUSTOMERNUMBER>
    <STATUS>Shipped</STATUS>
    <ORDERDATE>2003-01-06+06:00</ORDERDATE>
  </Entry>
  <Entry>
    <Customers xmlns="http://ws.wso2.org/dataservice/CustomerNameSQL">
      <Customer>
        <Name>Blauer See Auto, Co.</Name>
      </Customer>
    </Customers>
    <ORDERNUMBER>10101</ORDERNUMBER>
    <CUSTOMERNUMBER>128</CUSTOMERNUMBER>
    <STATUS>Shipped</STATUS>
    <ORDERDATE>2003-01-09+06:00</ORDERDATE>
  </Entry>
  <Entries>

With Multiple Parameters for the nested query:

For this I have modified the two tables by adding a new column as follows.

Alter table CUSTOMERS add ISACTIVE int DEFAULT(1);
Alter table ORDERS add ISACTIVE int DEFAULT(1);

The format of the mapping is as follows.
"@target_query_name": "$nested_query_parameter1->

outer_Query_result_element_name1,$nested_query_parameter2->outer_Query_result_element_name2"

<data name="TestNestedJSON" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin and c.ISACTIVE = :isactivein</sql>
      <result outputType="json">{"Customers": {"Customer": [{"Name": "$CUSTOMERNAME"}]}}</result>
      <param name="customerNumberin" sqlType="INTEGER"/>
      <param name="isactivein" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER,o.ISACTIVE from ORDERS o</sql>
      <result outputType="json">{"Entries": {"Entry": [{"ORDERNUMBER": "$ORDERNUMBER","ORDERDATE": "$ORDERDATE","STATUS": "$STATUS","CUSTOMERNUMBER": "$CUSTOMERNUMBER","@CustomerNameSQL": "$customerNumberin-&gt;customerNumber,$isactivein-&gt;isactive"}]}}</result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>

References:
[1] https://docs.wso2.com/display/DSS350/Defining+Nested+Queries
[2] https://docs.wso2.com/display/DSS350/JSON+Mapping

MyCodeIdeas

Sunday, July 3, 2016

WSO2 DSS - Working With Nested Queries