Query Service Deep Dive
In this article, we do a deep dive on the query service. We will start with an overview of the query service, followed by an examination of the various components of the query service. We will then present some example query services of varied complexity. Finally we will have a discussion on dynamic query.
We assume that you have had some hands-on experience with Service Builder, the development tool for data access service, and with the data access service. If not, please take a look at these tutorial first:
Get Started with Service Builder
Get Started with Data Access Service
Overview
Use
Query service is for retrieving data from the data source. It aims to provide the developer the flexibility to retrieve a relational data set into any shape that is needed by the application.
Components
Conceptually, the query service is composed of the following components:
- input
- output
- query
- input bindings, and
- output bindings
where the input and output are JSON strings specifying the structure and data fields of the input and output of the query service; and the query is a single SQL query for retrieving the data set for populating the output object or array. The input bindings are data mappings that map the query parameters to the data fields of the input, and the output bindings are data mappings that map the data fields of the output to the columns of the query.
Development
Service Builder provides a streamlined process for developing query service in a few simple steps:
- create the service
- specify the input and output
- develop a SQL query for the problem
- generate, review and edit, if needed, the input and output bindings; and
- generate, edit and run the test(s) for the service
The focus of the query service development is on developing the SQL query for populating the output object. As such, it is important to create a SQL development environment in VS Code with the various database extensions available, so that we have the visibility of database in VS Code and have the help of SQL syntax linting, SQL intellisense, SQL test, etc. Otherwise, we will need to compose and test the SQL in a specialized SQL editor, such as Oracle SQL developer, and then copy and paste into the query.sql file.
As the hard problem of object-relational transformation is abstracted away from the developer, the development of query service is expected to be much easier than the traditional ORM and raw SQL methods, especially when the output object is complex.
As soon as the query service is completed, we should try to deploy it into the remote workspace, which helps validate the service. We can then fix the issue, if any, and repeat the process until it is clean. Thereafter, we can proceed to adding the test.
Deployment
The query service is deployed on the data access server as an HTTP API. At development time, it is deployed into the remote workspace as it is developed. At runtime, it is deployed as part of the data access application.
Consumption
Since the query service is deployed as an HTTP API, it may be invoked by an HTTP request, like
POST: https://{baseUrl}/application/module/service
Content Type: application/json
{
"queryParameter": 123
}
The HTTP method is always POST. The body of the request is the input JSON. The baseUrl depends on the workspace or runtime instance.
Anatomy
Lets take a look at the query service through an example: getOrderByOrderNumber.
Service Files
Upon creation, we will get a set of service files as below:
- getOrderByOrderNumber
- service.json
- input.json
- output.json
- query.sql
- input-bindings.json
- output-bindings.json
- tests
The service.json files is a service descriptor generated by Service Builder and is not supposed to be modified, except the dynamic attribute if we are to make the query dynamic. The tests is a folder to hold the tests for the service that are to be added later. The rest of files are for the various components of the query service and are what are to be developed by the developer.
Input and Output
The input and output define the interface of the query service. They also specify the problem for the developer to solve. For our example, we have
Input:
Output:
{
"orderNumber": 10101,
"orderDate": "2023-01-15T00:00:00.000",
"customer": {
"customerNumber": 1,
"customerName": "Signal Gift Stores",
"customerIcon": "base64YmFzZTY0ZXhhbXBsZQ==",
"address": {
"addressLine1": "8489 Strong St.",
"addressLine2": "",
"city": "Las Vegas",
"state": "NV",
"country": "USA",
"postalCode": "83030"
}
},
"lines": [{
"orderLineNumber": 1,
"productCode": "S18_1749",
"productName": "1969 Harley Davidson Ultimate Chopper",
"qty": 1,
"price": 136.00
}]
}
The input must be a simple object. Array structure is not allowed at any level except for value lists, such as [1, 2, 3] and ["a", "b", "c"]. The output may be an object or Array. In our example, it is an Order object.
The JSON strings specify the structure and data fields of the input and output. For example, from the above, we see that "customer" and "address" are object structures, that "lines" is an array structure, that "orderNumber", "orderLineNumber", "qty" and "price" are numerical data fields, that "customerName", "addressLine1" and "addressLine2" are string data fields, that "orderDate" is a datetime data field, and that "customerIcon" is a binary data field, as the value string starts with "base64" followed by a base64-encoded string.
The data fields in the input and output JSON strings can be a number, a string, a datetime, a base64-encoded binary or value list of these types. The value null is not allowed in the JSON string, as the data type of the data field can not be inferred from this null value.
The output can include complex object and array structures, totally based on the needs of the application. More importantly, it is independent of the data model in the data source. In our example, the output includes an address structure inside customer. However, the data fields of both address and customer are from the customers table, as you may see later from the SQL query.
The input and output of the query service defines a contract between the query service and the client application. They are normally dictated by the need of the client application or agreed between the query service and the client application, prior to the service development. If the developer is responsible for development of both the query service and the client application, he/she may generate the output JSON from the application class, and thus save the work of developing the output object.
Query
The SQL query, along with the input and output bindings, is the developer's answer to the query problem. It must return a relational data set with data needed for populating all data fields of the output object/array in a single query by design. Therefore, the query service is expected to be very efficient. We will never see the infamous N+1 trick in query service.
For our example, we have
select o.orderNumber, orderDate,
c.customerNumber, c.customerName, null as customerIcon,
c.addressLine1, c.addressLine2, c.city,
c.state, c.country, c.postalCode,
od.orderLineNumber, od.productCode, p.productName,
od.quantityOrdered, od.priceEach
from orders o
join customers c on o.customerNumber = c.customerNumber
left join orderdetails od on od.orderNumber = o.orderNumber
left join products p on p.productCode = od.productCode
where o.orderNumber = :orderNumber
order by o.orderNumber, od.orderLineNumber
SQL is a very powerful language. With the many features SQL provides, there should be no issues to return the relational data set we need to populate the output object or array in most cases, although good SQL skill is assumed. One point worth noting is that SQL allows us to produce a data set from even a bad data model or database table design. The SQL query encapsulates the data model from the object model.
Input and Output Bindings
The input bindings indicate the source of data for the query parameters. The output bindings provide the critical information to construct the output object or array from the relational data set returned by the query.
For our example, we have the following generated from the input, output and query components.
Input Bindings:
Output Bindings:
| field | column | |
|---|---|---|
| 0 | .orderNumber | orderNumber |
| 1 | .orderDate | orderDate |
| 2 | .customer.customerNumber | customerNumber |
| 3 | .customer.customerName | customerName |
| 4 | .customer.customerIcon | customerIcon |
| 5 | .customer.address.addressLine1 | addressLine1 |
| 6 | .customer.address.addressLine2 | addressLine2 |
| 7 | .customer.address.city | city |
| 8 | .customer.address.state | state |
| 9 | .customer.address.country | country |
| 10 | .customer.address.postalCode | postalCode |
| 11 | .lines..orderLineNumber | orderLineNumber |
| 12 | .lines..productCode | productCode |
| 13 | .lines..productName | productName |
| 14 | .lines..qty | city |
| 15 | .lines..price | priceEach |
The input bindings map the query parameters to the date fields of the input object. Multiple parameters may be mapped to the same data fields, but all parameters must be mapped. The unmapped data fields are ignored.
The output bindings map the data fields of the output object to the columns of the query. Multiple data fields may be mapped to the same column, but all data fields must be mapped. The unmapped columns are ignored. If the column has an alias, the data field should be mapped to the alias name.
The data fields in the input and output bindings are designated with tailed JSON paths, such as ".customer.address.city" and ".lines..lineNumber" in the output bindings. The dot "." and double-dot ".." in the path strings denote an object and an array structure, respectively. The dot "." at beginning of the path string denotes the root object. If the output is an array, the path string will begin with double-dot "..".
The input and output bindings are not expected to be hand-coded from scratch, but generated with Service Builder from the input, output and query components first, and then reviewed and fixed, if necessary, by the developer. The Service Builder may not generate perfect mappings in all cases, and the developer is ultimately responsible for the accuracy of the data mapping. Proper column aliasing in the SQL query helps Service Builder generate correct mappings.
Test
A test may be added after the query service is completed. For our example, we have a test named testGetOrderByOrderNumber:
{ "name": "testGetOrderByOrderNumber", "input": { "orderNumber": 10010 }, "comments": "Modify the example test name and input." }
for testing the query service getOrderByOrderNumber.
For dynamic query service, multiple tests may be added, one for each of the supported query scenarios, including the scenario where none of the query parameter is provided.
Examples
The examples in this tutorial demonstrates what we can do with query service and how we could do it. The sample MySQL database used for the examples is the classicmodels database for MySQL Tutorial. The source code for these example query services can be found here.
Example #1 - getInventoryForProduct
Problem
To get quantity in stock by product code. The simplest query service that returns a single value.
Input:
Output:
The single value still needs to be wrapped in an object.
Query
Input and Output Bindings
Input Bindings:
| Parameter | field |
|---|---|
| productCode | .productCode |
Output Bindings:
| field | column |
|---|---|
| .quantityInStock | quantityInStock |
Example #2 - getProductCodes
Problem
To get a list of product codes. A simple query service that returns a value list.
Input:
Output:
The value list is a simple array of values.
Query
Input and Output Bindings
Input Bindings:
Output Bindings:
The input and output bindings for this example need to be manually added.
Example #3 - getProductCodesForProductLines
Problem
To get a list of product code for a list of product line names. A variation of Example #2 that features a list parameter in the query.
Input:
Output:
Query
Input and Output Bindings
Input:
Note how the parameter :productLines is used in the query and mapped to the list field .productLines.. of the input.
Output:
Example #4 - getCustomerByCustomerNumber
Problem
To get a customer object by customerNumber. A query service that returns a simple object.
Input:
Output:
{
"customerNumber": 112,
"customerName": "Land of Toys Inc.",
"contact": {
"firstName": "Joe",
"LastName": "Williamson",
"phone": "(171) 555-2282"
},
"address": {
"city": "NYC",
"state": "NY",
"country": "USA"
}
}
Query
select customerNumber, customerName,
contactLastName as lastName, contactFirstName as firstName, phone,
city, state, country
from customers
where customerNumber = :customerNumber
Input and Output Bindings
Input Bindings:
| Parameter | field |
|---|---|
| customerNumber | .customerNumber |
Output Bindings:
| field | column |
|---|---|
| .customerName | customerName |
| .contact.firstName | firstName |
| .contact.LastName | lastName |
| .contact.phone | phone |
| .address.city | city |
| .address.state | state |
| .address.country | country |
Note the column aliases
firstNameandlastNamein the SQL query to help the data mapping.
Example #5 - getCustomersByPostalCode
Problem
To return a list of customers by postalCode. A variation of Example #4 that returns an array of simple objects.
Input:
Output:
[{
"customerNumber": 1,
"customerName": "Land of Toys Inc.",
"contact": {
"firstName": "Joe",
"LastName": "Williamson",
"phone": "(171) 555-2282"
},
"address": {
"city": "NYC",
"state": "NY",
"country": "USA"
}
}]
Query
select customerNumber, customerName,
contactLastName as lastName, contactFirstName as firstName, phone,
city, state, country
from customers
where postalCode = :postalCode
Note that the
postalCodecolumn matching the:postalCodeparameter is not necessarily returned with the query.
Input and Output Bindings
Input Bindings:
| Parameter | field |
|---|---|
| postalCode | .postalCode |
Output Bindings:
| field | column |
|---|---|
| ..customerName | customerName |
| ..contact.firstName | firstName |
| ..contact.LastName | lastName |
| ..contact.phone | phone |
| ..address.city | city |
| ..address.state | state |
| ..address.country | country |
Note that the field path in the output bindings start with
".."versus"."in the previous example. now.
Example #6 - getOrderByOrderNumber
Problem
To get an order object by orderNumber. A query service that returns an object with array structure. This is the example used in the Anatomy section, and is re-listed here.
Input:
Output:
{
"orderNumber": 123,
"orderDate": "2023-01-15T00:00:00.000",
"customer": {
"customerNumber": 1,
"customerName": "Signal Gift Stores",
"icon": "base64YmFzZTY0ZXhhbXBsZQ==",
"address": {
"addressLine1": "8489 Strong St.",
"addressLine2": "",
"city": "Las Vegas",
"state": "NV",
"country": "USA",
"postalCode": "83030"
}
},
"lines": [{
"orderLineNumber": 1,
"productCode": "S18_1749",
"productName": "1969 Harley Davidson Ultimate Chopper",
"qty": 1,
"price": 136.00
}]
}
Query
select o.orderNumber, orderDate,
c.customerNumber, c.customerName, null as icon,
c.addressLine1, c.addressLine2, c.city,
c.state, c.country, c.postalCode,
od.orderLineNumber, od.productCode, p.productName,
od.quantityOrdered, od.priceEach
from orders o
join customers c on o.customerNumber = c.customerNumber
left join orderdetails od on od.orderNumber = o.orderNumber
left join products p on p.productCode = od.productCode
where o.orderNumber = :orderNumber
Input and Output Bindings
Input Bindings:
| Parameter | field |
|---|---|
| orderNumber | .orderNumber |
Output Bindings:
| field | column |
|---|---|
| .orderNumber | orderNumber |
| .orderDate | orderDate |
| .customer.customerNumber | customerNumber |
| .customer.customerName | customerName |
| .customer.customerIcon | customerIcon |
| .customer.address.addressLine1 | addressLine1 |
| .customer.address.addressLine2 | addressLine2 |
| .customer.address.city | city |
| .customer.address.state | state |
| .customer.address.country | country |
| .customer.address.postalCode | postalCode |
| .lines..orderLineNumber | orderLineNumber |
| .lines..productCode | productCode |
| .lines..productName | productName |
| .lines..qty | city |
| .lines..price | priceEach |
Example #7 - getOrderByCustomerNumber
Problem
To return a list of orders by customerNumber. A variation of Example #6 that returns a list of objects with array structure.
Input:
Output:
[{
"orderNumber": 123,
"orderDate": "2023-01-15T00:00:00.000",
"customer": {
"customerNumber": 1,
"customerName": "Signal Gift Stores",
"icon": "base64YmFzZTY0ZXhhbXBsZQ==",
"address": {
"addressLine1": "8489 Strong St.",
"addressLine2": "",
"city": "Las Vegas",
"state": "NV",
"country": "USA",
"postalCode": "83030"
}
},
"lines": [{
"orderLineNumber": 1,
"productCode": "S18_1749",
"productName": "1969 Harley Davidson Ultimate Chopper",
"qty": 1,
"price": 136.00
}]
}]
Query
select o.orderNumber, orderDate,
c.customerNumber, c.customerName, null as icon,
c.addressLine1, c.addressLine2, c.city,
c.state, c.country, c.postalCode,
od.orderLineNumber, od.productCode, p.productName,
od.quantityOrdered as qty, od.priceEach
from orders o
join customers c on o.customerNumber = c.customerNumber
left join orderdetails od on od.orderNumber = o.orderNumber
left join products p on p.productCode = od.productCode
where o.customerNumber = :customerNumber
Input and Output Bindings
Input Bindings:
| Parameter | field |
|---|---|
| customerNumber | .customerNumber |
Output Bindings:
| field | column |
|---|---|
| ..orderNumber | orderNumber |
| ..orderDate | orderDate |
| ..customer.customerNumber | customerNumber |
| ..customer.customerName | customerName |
| ..customer.customerIcon | customerIcon |
| ..customer.address.addressLine1 | addressLine1 |
| ..customer.address.addressLine2 | addressLine2 |
| ..customer.address.city | city |
| ..customer.address.state | state |
| ..customer.address.country | country |
| ..customer.address.postalCode | postalCode |
| ..lines..orderLineNumber | orderLineNumber |
| ..lines..productCode | productCode |
| ..lines..productName | productName |
| ..lines..qty | city |
| ..lines..price | priceEach |
Example #8 - getSalesRepsByOfficeCode
Problem
To get a list of sales reps by office code. A query service that returns a list of complex objects with multiple array structures.
Input:
Output:
[{
"employeeNumber": 1,
"lastName": "John",
"firstName": "Smith",
"office": {
"officeCode": 2,
"city": "NYC",
"country": "USA"
},
"customers": [{
"customerNumber": 1,
"customerName": "Land of Toys Inc.",
"contact": {
"firstName": "Joe",
"LastName": "Williamson",
"phone": "(171) 555-2282"
},
"address": {
"city": "NYC",
"state": "NY",
"country": "USA"
}
}],
"sales": [{
"productCode": 1,
"productName": "1968 Ford Mustang",
"qty": 100,
"dollarAmount": 12345.67
}]
}]
Query
select e.employeeNumber, e.lastName, e.firstName,
o.officeCode, o.city, o.country,
c.customerNumber, c.customerName,
c.contactLastName, c.contactFirstName, c.phone,
c.city as customerCity, c.state as customerState, c.country as customerCountry,
s.productCode, s.productName, s.qty, s.dollarAmount
from employees e
join offices o on o.officeCode = e.officeCode
left join customers c on c.salesRepEmployeeNumber = e.employeeNumber
left join (
select c2.salesRepEmployeeNumber, p.productCode, p.productName, sum(od.quantityOrdered) as qty, sum(od.priceEach*od.quantityOrdered) as dollarAmount
from customers c2
join orders o on o.customerNumber = c2.customerNumber
join orderdetails od on od.orderNumber = o.orderNumber
join products p on p.productCode = od.productCode
group by c2.salesRepEmployeeNumber, p.productCode, p.productName
) s on s.salesRepEmployeeNumber = e.employeeNumber
where e.officeCode = :officeCode
and e.jobTitle = 'Sales Rep'
order by e.employeeNumber
This query showcases the power of SQL in fabricating data for the output structure. The sales data is completely computed from the sales orders.
Input and Output Bindings
Input Bindings:
| Parameter | field |
|---|---|
| officeCode | .officeCode |
Output Bindings:
| field | column | |
|---|---|---|
| 0 | ..officeCode | officeCode |
| 1 | ..city | city |
| 2 | ..country | country |
| 3 | ..salesReps..employeeNumber | employeeNumber |
| 4 | ..salesReps..lastName | lastName |
| 5 | ..salesReps..firstName | firstName |
| 6 | ..salesReps..customers..customerNumber | customerNumber |
| 7 | ..salesReps..customers..customerName | customerName |
| 8 | ..salesReps..customers..contact.firstName | contactFirstName |
| 9 | ..salesReps..customers..contact.LastName | contactLastName |
| 10 | ..salesReps..customers..contact.phone | phone |
| 11 | ..salesReps..customers..address.city | customerCity |
| 12 | ..salesReps..customers..address.state | customerState |
| 13 | ..salesReps..customers..address.country | customerCountry |
| 14 | ..salesReps..sales..productCode | productCode |
| 15 | ..salesReps..sales..productName | productName |
| 16 | ..salesReps..sales..qty | qty |
| 17 | ..salesReps..sales..dollarAmount | dollarAmount |
In this example,
Service builderis able to match the contact name correctly, but not the three customer address fields, which have to manually fixed.
Example #9 - listSalesRepsByOffice
Problem
To list sales reps for all offices. A query service that returns a deep-nested complex structure: array of array of array.
Input:
Output:
[{
"officeCode": 2,
"city": "NYC",
"country": "USA",
"salesReps": [{
"employeeNumber": 1,
"lastName": "John",
"firstName": "Smith",
"customers": [{
"customerNumber": 1,
"customerName": "Land of Toys Inc.",
"contact": {
"firstName": "Joe",
"LastName": "Williamson",
"phone": "(171) 555-2282"
},
"address": {
"city": "NYC",
"state": "NY",
"country": "USA"
}
}],
"sales": [{
"productCode": 1,
"productName": "1968 Ford Mustang",
"qty": 100,
"dollarAmount": 12345.67
}]
}]
}]
Query
select o.officeCode, o.city, o.country,
e.employeeNumber, e.lastName, e.firstName,
c.customerNumber, c.customerName,
c.contactLastName, c.contactFirstName, c.phone,
c.city as customerCity, c.state as customerState, c.country as customerCountry,
s.productCode, s.productName, s.qty, s.dollarAmount
from offices o
join employees e on o.officeCode = e.officeCode
left join customers c on c.salesRepEmployeeNumber = e.employeeNumber
left join (
select c2.salesRepEmployeeNumber, p.productCode, p.productName, sum(od.quantityOrdered) as qty, sum(od.priceEach*od.quantityOrdered) as dollarAmount
from customers c2
join orders ord on ord.customerNumber = c2.customerNumber
join orderdetails od on od.orderNumber = ord.orderNumber
join products p on p.productCode = od.productCode
group by c2.salesRepEmployeeNumber, p.productCode, p.productName
) s on s.salesRepEmployeeNumber = e.employeeNumber
where e.jobTitle = 'Sales Rep'
order by o.officeCode, e.employeeNumber
Input and Output Bindings
Input Bindings:
Output Bindings:
| field | column | |
|---|---|---|
| 0 | ..officeCode | officeCode |
| 1 | ..city | city |
| 2 | ..country | country |
| 3 | ..salesReps..employeeNumber | employeeNumber |
| 4 | ..salesReps..lastName | lastName |
| 5 | ..salesReps..firstName | firstName |
| 6 | ..salesReps..customers..customerNumber | customerNumber |
| 7 | ..salesReps..customers..customerName | customerName |
| 8 | ..salesReps..customers..contact.firstName | contactFirstName |
| 9 | ..salesReps..customers..contact.LastName | contactLastName |
| 10 | ..salesReps..customers..contact.phone | phone |
| 11 | ..salesReps..customers..address.city | customerCity |
| 12 | ..salesReps..customers..address.state | customerState |
| 13 | ..salesReps..customers..address.country | customerCountry |
| 14 | ..salesReps..sales..productCode | productCode |
| 15 | ..salesReps..sales..productName | productName |
| 16 | ..salesReps..sales..qty | qty |
| 17 | ..salesReps..sales..dollarAmount | dollarAmount |
In this example,
Service builderis unable to match the three customer address fields and they have to manually fixed.
Example #10 - getEmployeeHierarchy
Problem
To get the employee hierarchy. A query service that returns a 5-level hierarchy structure, showcasing the capability of query service to produce the deep-nested array structure. This method is a bit cumbersome in terms of the way to specify both output and SQL query, but it does provide a working solution. The depth of the hierarchical structure is limited and pre-specified through the output.
Input:
Output:
[{
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President",
"employees": [{
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President",
"employees": [{
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President",
"employees": [{
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President",
"employees": [{
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President",
"employees": [{
}]
}]
}]
}]
}]
}]
Query
select a.employeeNumber, a.lastName,
a.firstName, a.jobTitle,
b.employeeNumber as eEmployeeNumber, b.lastName as eLastName,
b.firstName as eFirstName, b.jobTitle as eJobTitle,
c.employeeNumber as eeEmployeeNumber, c.lastName as eeLastName,
c.firstName as eeFirstName, c.jobTitle as eeJobTitle,
d.employeeNumber as eeeEmployeeNumber, d.lastName as eeeLastName,
d.firstName as eeeFirstName, d.jobTitle as eeeJobTitle,
e.employeeNumber as eeeeEmployeeNumber, e.lastName as eeeeLastName,
e.firstName as eeeeFirstName, e.jobTitle as eeeeJobTitle
from employees a
left join employees b on a.employeeNumber = b.reportsTo
left join employees c on b.employeeNumber = c.reportsTo
left join employees d on c.employeeNumber = d.reportsTo
left join employees e on d.employeeNumber = e.reportsTo
where a.reportsTo is null
Input and Output Bindings
Input Bindings:
Output Bindings:
| field | column | |
|---|---|---|
| 0 | ..employeeNumber | employeeNumber |
| 1 | ..firstName | firstName |
| 2 | ..lastName | lastName |
| 3 | ..jobTitle | jobTitle |
| 4 | ..employees..employeeNumber | eEmployeeNumber |
| 5 | ..employees..firstName | eFirstName |
| 6 | ..employees..lastName | eLastName |
| 7 | ..employees..jobTitle | eJobTitle |
| 8 | ..employees..employees..employeeNumber | eeEmployeeNumber |
| 9 | ..employees..employees..firstName | eeFirstName |
| 10 | ..employees..employees..lastName | eeLastName |
| 11 | ..employees..employees..jobTitle | eeJobTitle |
| 12 | ..employees..employees..employees..employeeNumber | eeeEmployeeNumber |
| 13 | ..employees..employees..employees..firstName | eeeFirstName |
| 14 | ..employees..employees..employees..lastName | eeeLastName |
| 15 | ..employees..employees..employees..jobTitle | eeeJobTitle |
| 16 | ..employees..employees..employees..employees..employeeNumber | eeeeEmployeeNumber |
| 17 | ..employees..employees..employees..employees..firstName | eeeeFirstName |
| 18 | ..employees..employees..employees..employees..lastName | eeeeLastName |
| 19 | ..employees..employees..employees..employees..jobTitle | eeeeJobTitle |
Example #11 - listEmployees
Problem
To list employees with rich manager info. A query service that returns an array of objects with a complex array-in-object structure.
Input:
Output:
[{
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President",
"manager": {
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President",
"manager": {
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President"
},
"employees": [{
"employeeNumber": 1,
"firstName": "John",
"lastName": "Smith",
"jobTitle": "President"
}]
}
}]
Query
select e.employeeNumber, e.lastName,
e.firstName, e.jobTitle,
m.employeeNumber as mEmployeeNumber, m.lastName as mLastName,
m.firstName as mFirstName, m.jobTitle as mJobTitle,
e2.employeeNumber as meEmployeeNumber, e2.lastName as meLastName,
e2.firstName as meFirstName, e2.jobTitle as meJobTitle,
m2.employeeNumber as mmEmployeeNumber, m2.lastName as mmLastName,
m2.firstName as mmFirstName, m2.jobTitle as mmJobTitle
from employees e
left join employees m on m.employeeNumber = e.reportsTo
left join employees e2 on m.employeeNumber = e2.reportsTo
left join employees m2 on m2.employeeNumber = m.reportsTo
Input and Output Bindings
Input Bindings:
Output Bindings:
| field | column | |
|---|---|---|
| 0 | ..employeeNumber | employeeNumber |
| 1 | ..firstName | firstName |
| 2 | ..lastName | lastName |
| 3 | ..jobTitle | jobTitle |
| 4 | ..manager.employeeNumber | mEmployeeNumber |
| 5 | ..manager.firstName | mFirstName |
| 6 | ..manager.lastName | mLastName |
| 7 | ..manager.jobTitle | mJobTitle |
| 8 | ..manager.manager.employeeNumber | mmEmployeeNumber |
| 9 | ..manager.manager.firstName | mmFirstName |
| 10 | ..manager.manager.lastName | mmLastName |
| 11 | ..manager.manager.jobTitle | mmJobTitle |
| 12 | ..manager.employees..employeeNumber | meEmployeeNumber |
| 13 | ..manager.employees..firstName | meFirstName |
| 14 | ..manager.employees..lastName | meLastName |
| 15 | ..manager.employees..jobTitle | meJobTitle |
Dynamic Query
Query service supports dynamic query, or a query that varies at runtime with the parameters supplied in the service request. The purpose is to avoid repeat of similar queries that vary only by small difference. For example, we may need a query getCustomersByCity and a query getCustomersByPostal. In this case, instead of two separate query services, we may create one dynamic query getCustomersByCityOrPostalCode to cover both use cases.
The dynamic query feature of the query service may be enabled by changing the dynamic attribute to true in the service.json file. Once this feature is enabled, the actual query that gets executed for the service changes with the parameters present in the service request.
In the case of getCustomersByCityOrPostalCode, assume that we have
Input:
Output:
[{
"customerNumber": 1,
"customerName": "Land of Toys Inc.",
"address": {
"city": "NYC",
"state": "NY",
"postalCode": "90001"
}
}]
Query:
select customerNumber, customerNumber, city, state, postalCode
from customers
where 1 = 1
and city = :city
and postalCode = :postalCode
If we call the service with an input like:
the query will change to
select customerNumber, customerNumber, city, state, postalCode
from customers
where 1 = 1
and city = :city
The line and postalCode = :postalCode containing parameter :postalCode is dropped from the original query, because the data field postalCode mapped to parameter :postalCode is not present in the service input, and thus it is now a query by city.
Similarly, if instead we call the service with an input like:
the query will change to
select customerNumber, customerNumber, city, state, postalCode
from customers
where 1 = 1
and postalCode = :postalCode
the line and a.city = :city containing parameter :city is dropped from the original query, because the data field city mapped to parameter :cityis not present in the service input, and thus it becomes a query by postalCode.
Further, if we call the service with an input like:
neither city nor postalCode is supplied, the query will change to
It is a query for all customers. If this is not the behavior we want and instead we want the query service to return nothing if neither city nor postalCode is supplied with the call, the query could be written as:
select customerNumber, customerNumber, city, state, postalCode
from customers
where (
1 = 0
or :city is not null
or :postalCode is not null
)
and city = :city
and postalCode = :postalCode
if neither city nor postalCode is supplied with the service request, the query changes to
and returns nothing.
Conclusions
Query service may be used to retrieve data from a single value to very complex objects. As the relational-to-object data transformation is abstracted away, the main work of query service development is to prepare the SQL query to retrieve the data set for populating the output object. It is important for the developer to create a SQL development environment in VS Code with the database extensions available.