Apisero is now part of NTT DATA - Learn more.

Search
Close this search box.

Filter Data Using HTTP Query Params (Like ODATA)

Author: Ayesha Saoji

 

Objective:

To query databases using HTTP Request and query Params using filter expressions similar to ODATA.
Just as we use OData to query data uniformly in URL, we can replicate the same using HTTP filter operators and query Params.

For Ex: Query DataBase using filter operators in HTTP Request.

Sample HTTP Request Format
http://localhost:8081/Customers?$format=json&$select=CustomerID,CompanyName,Address,City,Country,Phone,Fax&$filter=Country eq ‘France’ &$orderby=Phone&$top=10&$skip=2

Operators Used

  • $format
  • $select
  • $filter
  • $orderby
  • $top
  • $skip

Flow Design Components

  1. Listener to listen to endpoint /Customers at port 8081.
  2. Transform Message Component to Write DataWeave Transformation on an HTTP Request to form a SQL select query to retrieve expected results.
  3. Select DataBase Component.
  4. Transform Message Component to Display retrieved data from the database.
Flow Design Diagram

 

 

Observe the content of the Query String

 

 

Observe the payload

 

 

Payload contains the resultset from the database

 

 

PostMan request result
{
    "Customers": [
        {
            "Country": "France",
            "Address": "184, chausse de Tournai",
            "City": "Lille",
            "Phone": "20.16.10.16",
            "CompanyName": "Folies gourmandes",
            "CustomerID": "FOLIG",
            "Fax": "20.16.10.17"
        },
        {
            "Country": "France",
            "Address": "59 rue de l'Abbaye",
            "City": "Reims",
            "Phone": "26.47.15.10",
            "CompanyName": "Vins et alcools Chevalier",
            "CustomerID": "VINET",
            "Fax": "26.47.15.11"
        },
        {
            "Country": "France",
            "Address": "67, avenue de l'Europe",
            "City": "Versailles",
            "Phone": "30.59.84.10",
            "CompanyName": "La corne d'abondance",
            "CustomerID": "LACOR",
            "Fax": "30.59.85.11"
        },
        {
            "Country": "France",
            "Address": "54, rue Royale",
            "City": "Nantes",
            "Phone": "40.32.21.21",
            "CompanyName": "France restauration",
            "CustomerID": "FRANR",
            "Fax": "40.32.21.20"
        },
        {
            "Country": "France",
            "Address": "67, rue des Cinquante Otages",
            "City": "Nantes",
            "Phone": "40.67.88.88",
            "CompanyName": "Du monde entier",
            "CustomerID": "DUMON",
            "Fax": "40.67.89.89"
        },
        {
            "Country": "France",
            "Address": "1 rue Alsace-Lorraine",
            "City": "Toulouse",
            "Phone": "61.77.61.10",
            "CompanyName": "La maison d'Asie",
            "CustomerID": "LAMAI",
            "Fax": "61.77.61.11"
        },
        {
            "Country": "France",
            "Address": "2, rue du Commerce",
            "City": "Lyon",
            "Phone": "78.32.54.86",
            "CompanyName": "Victuailles en stock",
            "CustomerID": "VICTE",
            "Fax": "78.32.54.87"
        },
        {
            "Country": "France",
            "Address": "24, place Klber",
            "City": "Strasbourg",
            "Phone": "88.60.15.31",
            "CompanyName": "Blondesddsl pre et fils",
            "CustomerID": "BLONP",
            "Fax": "88.60.15.32"
        },
        {
            "Country": "France",
            "Address": "12, rue des Bouchers",
            "City": "Marseille",
            "Phone": "91.24.45.40",
            "CompanyName": "Bon app'",
            "CustomerID": "BONAP",
            "Fax": "91.24.45.41"
        }
    ]
}

 

Cross verification – DataBase query result same as PostMan result

 

 

DataWeave code for SQL query creation
%dw 2.0
import * from dw::core::Arrays
output application/java
var ArrParams = attributes.queryString splitBy "&"

//This is how ArrayParams looks like.
[$format=json,$select=CustomerID,CompanyName,Address,City,Country,Phone,Fax, $filter=Country eq 'France' , $orderby=Phone, $top=10, $skip=2]

var SQLFilter = (filter) -> 
      
     filter replace "eq null" with "is null" 
     replace "ne null" with "is not null" 
     replace " eq " with " like " 
     replace " ne " with " != " 
     replace " gt " with " > " 
     replace " lt " with " < " 
     replace " ge " with " >= " 
     replace " le " with " <= " 
     replace " and " with " AND " 
     replace " or " with " OR " 
     
var toSQLOrderBy = (orderby ) -> if(orderby != "") (" ORDER BY " ++ (orderby replace "=" with " ")) else ""

var toSQLSkipAndTop = (top, skip) -> if(top != "" and skip != "") " LIMIT $top OFFSET $skip" else if (top == "" and skip != "") " LIMIT 2147483647 OFFSET $skip" else if (top != "" and skip == "") " LIMIT $top"  else ""

var toSQLWhere = (filter) -> if(filter !=  "") " WHERE " ++ SQLFilter(filter) else ""
---

using(SelectIndex = ArrParams indexWhere (item) -> item contains "select") 
using(ArrSelect =  ArrParams[SelectIndex] splitBy "=")
using(select = ArrSelect[1])

using(FilterIndex = ArrParams indexWhere (item) -> item contains "filter") 
using(ArrFilter =  ArrParams[FilterIndex] splitBy "=")
using(filter = ArrFilter[1])

using(OrderByIndex = ArrParams indexWhere (item) -> item contains "orderby") 
using(ArrOrderBy =  ArrParams[OrderByIndex] splitBy "=")
using(orderby = ArrOrderBy[1])

using(TopIndex = ArrParams indexWhere (item) -> item contains "top") 
using(ArrTop =  ArrParams[TopIndex] splitBy "=")
using(top = ArrTop[1])

using(SkipIndex = ArrParams indexWhere (item) -> item contains "skip") 
using(ArrSkip =  ArrParams[SkipIndex] splitBy "=")
using(skip = ArrSkip[1])

"SELECT " ++ select ++ " FROM customers" ++ (toSQLWhere(filter)) ++ (toSQLOrderBy(orderby)) ++ (toSQLSkipAndTop(top,skip)) 

 

Link to use contains function in Mule 4

Contains
https://docs.mulesoft.com/mule-runtime/4.3/dw-core-functions-contains
Dataweave Function: https://docs.mulesoft.com/mule-runtime/4.3/dataweave-functions
SplitBy
https://docs.mulesoft.com/mule-runtime/4.3/dw-core-functions-splitby

Conclusion

This is how we get data from a database using HTTP requests and filter operators. We have explored the contains operator, dataweave functions and splitby using operator, etc.

We use cookies on this site to enhance your user experience. For a complete overview of how we use cookies, please see our privacy policy.