WSO2 REST API: How to return more than one row in the GET request

Question

Could you please advice how to get more than one row? My API source:

<inSequence xmlns="http://ws.apache.org/ns/synapse">
<property expression="json-eval($.vendorId)" name="vendorId" scope="default" type="STRING"/>
  <dblookup>
        <connection>
            <pool>
                <dsName>MyDS</dsName>
            </pool>
        </connection>
        <statement>
            <sql><![CDATA[select * from db_view v where v.vendorId = nvl(?, v.vendorId)]]></sql>
            <parameter expression="$ctx:vendorId" type="VARCHAR"/>
            <result column="vendorId" name="vendorId"/>
            <result column="vendorName" name="vendorName"/>
        </statement>
    </dblookup>
    <filter xpath="boolean($ctx:vendorId)">
        <then>
            <payloadFactory media-type="json">
                <format>{"result":     { "vendorId" : "$1","vendorName" : "$2"}}</format>
                <args>
                    <arg evaluator="xml" expression="$ctx:vendorId" literal="false"/>
                    <arg evaluator="xml" expression="$ctx:vendorName" literal="false"/>
                </args>
            </payloadFactory>
        </then>
        <else>
            <payloadFactory media-type="json">
                <format>{"response" : { "code" : "01","message" : "fail"}}</format>
            </payloadFactory>
        </else>
    </filter>
</inSequence>

My db view returns 5 rows, but I get only the first row from the GET request:

{"result": { "vendorId" : "123",        "vendorName" : "My Vendor N1"    }}

Also if I pass the parameter vendorId = 321, the api should return 0 rows. At the current moment it ALWAYS returns the first row from view. Why and how to make it work? Thanks in advance

CALLOUT Mediator:

<resource methods="GET" uri-template="/get/supplier/{filterQuery}">
      <inSequence>
         <property name="operation" value="view" scope="default" type="STRING"/>
         <log level="full" separator="|">
            <property name="operation" expression="get-property('operation')"/>
            <property name="step" value="request"/>
         </log>
         <property name="filterQuery" expression="get-property('uri.var.filterQuery')" scope="default" type="STRING"/>
         <log level="full" separator="|">
            <property name="filterQuery" expression="$ctx:filterQuery"/>
         </log>
         <callout serviceURL="http://...:8280/services/serviceName" action="/vendors/$ctx:filterQuery">
            <source type="envelope"/>
            <target key="response"/>
         </callout>
         <log level="custom" separator="|">
            <property name="MESSAGE" expression="$ctx:response"/>
         </log>
         <property name="res" expression="$ctx:response" scope="default" type="OM"/>
         <property name="NO_ENTITY_BODY" scope="axis2" action="remove"/>
         <property name="RESPONSE" value="true" scope="default" type="STRING"/>
         <property name="messageType" value="application/json" scope="axis2" type="STRING"/>
         <property name="contentType" value="application/json" scope="transport" type="STRING"/>
         <payloadFactory media-type="json">
            <format>{"vendorId": "$1","vendorName": "$2"}</format>
            <args/>
         </payloadFactory>
         <log level="full" separator="|">
            <property name="operation" expression="$ctx:operation"/>
            <property name="step" value="response"/>
         </log>
         <respond/>
      </inSequence>
      <faultSequence>
         <payloadFactory media-type="json">
            <format>{"response" : {"code" : "01", "message" : "fail"}}</format>
            <args/>
         </payloadFactory>
      </faultSequence>
   </resource>

Answer 1

You can not use dblookup madiator as mentioned but you can use DataSources. Brief datasource example for Get Method dataservice;

<data name="RDBMSDataService" serviceStatus="active" transports="http https local">
   <config enableOData="false" id="Datasource">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/Employees</property>
      <property name="username">root</property>
      <property name="password">password</property>
   </config>
   <resource method="GET" path="Employee/{EmployeeNumber}">
      <call-query href="GetEmployeeDetails">
         <with-param name="EmployeeNumber" query-param="EmployeeNumber"/>
      </call-query>
   </resource>
   <query id="GetEmployeeDetails" useConfig="Datasource">
      <sql>select EmployeeNumber, FirstName, LastName, Email, Salary from Employees where EmployeeNumber=:EmployeeNumber</sql>
      <result outputType="json">{
   "Employees":{
      "Employee":[
         {
            "EmployeeNumber":"$EmployeeNumber",
            "FirstName":"$FirstName",
            "LastName":"$LastName",
            "Email":"$Email",
            "Salary":"$Salary"
         }
      ]
   }
}</result>
      <param name="EmployeeNumber" sqlType="STRING"/>
   </query>
</data>

You can create this .dbs file and deploy it. Then you can call this component like http://localhost:8290/services/RDBMSDataService/Employee/{EmployeeNumber} with specific parametres then it will return json :

{
    "Employees": {
        "Employee": [
            {
                "EmployeeNumber": "5012",
                "FirstName": "Will",
                "LastName": "Smith",
                "Email": "will@smith.com",
                "Salary": "13500.0"
            },
            {
                "EmployeeNumber": "5013",
                "FirstName": "Parker",
                "LastName": "Peter",
                "Email": "peter@parker.com",
                "Salary": "15500.0"
            }
        ]
    }
}

You can take the value from context like :

<property expression="json-eval($.Employees)" name="EmployeeList" scope="default" type="STRING"/>

Answer 2

This is normal behavior. As you can read in DBLookup Mediator documentation :

The DBLookup mediator can set a property from one row in a result set. It cannot return multiple rows. If you need to get multiple records, or if you have a table with multiple parameters (such as URLs), you can use the WSO2 Data Services Server to create a data service and invoke that service from the ESB using the Callout mediator instead.