How to use dynamic SQL filter in WSO2 Data Services?

I’m trying to expose a SQL SELECT statement as a WSO2 Data Service with optional filters via query parameters. My goal is to build a dynamic query depending on the parameters passed to the API.

Here is my data service query definition:

<query id="selectprofile" useConfig="default">
    <sql>
        SELECT BADLOGINS, EMAIL, FRAMED_ROUTE, FULLNAME FROM USERS :filterQuery
    </sql>
    <result outputType="json">
        {
            "accounts": {
                "account": [{
                    "BADLOGINS": "$BADLOGINS",
                    "EMAIL": "$EMAIL",
                    "FRAMED_ROUTE": "$FRAMED_ROUTE",
                    "FULLNAME": "$FULLNAME"
                }]
            }
        }
    </result>
    <param name="filterQuery" sqlType="QUERY_STRING"/>
</query>

What I’ve tried

• Using :filterQuery to inject the optional filter as a dynamic clause.
• Invoking the service with FULLNAME="AHMED" as a parameter.
• Trying to pass the full condition string manually as a value for filterQuery (e.g., filterQuery=WHERE FULLNAME='AHMED').
• Changing the SQL and query param syntax based on different community examples.

My questions

1.How do I correctly pass dynamic filters into a WSO2 Data Service using sqlType="QUERY_STRING"?

2. Is it possible to pass individual parameters (e.g., FULLNAME) and build the SQL dynamically?

3. Should the filterQuery be passed as a literal string (WHERE ...) or as key-value query params?

The resource path should only contain the resource name if you need to use a query param. In your case, the resource element should be as follows,

<resource method="GET" path="selectprofile">
  <call-query href="selectprofile">
     <with-param name="filterQuery" query-param="filterQuery"/>
  </call-query>
</resource>

According to the above example, when invoking the data service you will need to pass a query parameter named filterQuery. This parameter should contain the complete query you need to use(It seems you have missed the WHERE clause).

localhost:8290/services/MYSQLDataService/selectprofile?filterQuery=WHERE FULLNAME="AHMED"

For more information check Exposing Data as a REST Resource