Skip to content

Data Access Service Reference Guide

Query Service

The query service is for retrieving ad hoc objects from database. It comprises

  • an input
  • an output
  • a query
  • an input bindings component that maps the input fields to the query parameters, and
  • an output bindings component that maps the output fields to the query columns

The query service supports a single query operation.

File Structure

The file structure of query service is as follows:

myQueryService/
    service.json
    input.json
    output.json
    query.sql
    input-bindings.json
    output-bindings.json
    tests/
        testMyQueryService.json

This file structure is generated when the service is created. There is one file corresponding to each of component of the query service. The user is expected to complete the input, output, query, input bindings and output bindings components, with tools provided by Service Builder and third-party, such as Database Client and JSON Grid Viewer, etc., available in the VSCode environment.

Example

The following is an exemplary query service to retrieve a list of customer objects by city and state.

Input

{
    "city": "Los Angeles",
    "state": "CA"
}

The input of the query service is a simple object carrying the query parameters.

Output

[{
    "customerId": 123,
    "customerName": "John",
    "address": {
        "street": "123 main st",
        "city": "Los Angeles",
        "state": "CA"
    }
}]

The output of the query service may be an object or array of objects. In this example, the output is an array of customer objects.

If the query service is to return a single customer object by customerId instead, the output would be an object as follows:

{
    "customerNumber": 123,
    "customerName": "John",
    "address": {
        "address": "123 main st",
        "city": "Los Angeles",
        "state": "CA"
    }
}

Query

SELECT customerNumber, customerName,
       addressLine1 as address, city, state    
  FROM classicmodels.customers
 WHERE state = :state
   AND city = :city

The query component of the query service is a single SELECT statement. The query parameters in the SQL statement are prefixed with ":".

Besides the value parameters like :state in the above query, DAS also supports list parameters. For example, the :customerNames in the following query is a list parameter:

SELECT customerNumber, customerName,
       addressLine1 as address, city, state    
  FROM classicmodels.customers
 WHERE customerName in ( :customerNames )

The list parameter shall be mapped to a list field in the input. For example, the .customerNames field in the following input object:

{
    "customerNames": ["John", "Smith"]
}

Input Bindings

[
    {
        "parameter": "city",
        "field": ".city"
    },
    {
        "parameter": "state",
        "field": ".state"
    }
]

The input bindings of the query service map the query parameters to the input fields. The parameter is the name of the parameter. The field is the json path of the data field in the input object.

Output Bindings

[
    {
        "field": "..customerNumber",
        "column": "customerNumber"
    },
    {
        "field": "..customerName",
        "column": "customerName"
    },
    {
        "field": "..address.address",
        "column": "address"
    },
    {
        "field": "..address.city",
        "column": "city"
    }
    {
        "field": "..address.state",
        "column": "state"
    }
]

The output bindings of the query service map the query columns to the output fields. The column is the alias or the name of the column; the field is the json path of the data field in the output object.

The user is expected to generate the input and output bindings from the input, output and query with Service Builder, and then review and edit them if needed. The Service Builder is expected to generate the input and output bindings correctly in normal case, but the user is ultimately responsible for the accuracy of the input and output bindings.

Dynamic Query

DAS supports dynamic query, or query that changes at runtime with the parameters present in the service request. The dynamic query may be enabled by setting the dynamic property to true in the service file. For example,

{
    "name": "getCustomers",
    "type": "query",
    "description": "query service. Don't modify this file except the dynamic field!",
    "input": "./input.json",
    "output": "./output.json",
    "query": "./query.sql",

    "dynamic": true,

    "inputBindings": "./input-bindings.json",
    "outputBindings": "./output-bindings.json"
}

The dynamic query changes at runtime with the parameters present in the service request. if a parameter is not present in the request, the line(s) containing the parameter is dropped from the query.

For example, if the query service in the example above made dynamic and a service request is initiated with input as:

{
    "state": "CA"
}

The original query

SELECT customerNumber, customerName,
       addressLine1 as address, city, state    
  FROM classicmodels.customers
 WHERE state = :state
   AND city = :city

will change into

SELECT customerNumber, customerName,
       addressLine1 as address, city, state    
  FROM classicmodels.customers
 WHERE state = :state

which is a query by state, with the line containing :city dropped from the query due to the absence of city parameter in the input.

Or if a service request is made with an empty input as:

{}

The query will change into

SELECT customerNumber, customerName,
       addressLine1 as address, city, state    
  FROM classicmodels.customers

which is a query for all customers.

It follows that, if a service request is made with an input as:

{"city": "Los Angels"}

the query will become

SELECT customerNumber, customerName,
       addressLine1 as address, city, state    
   AND city = :city

This is an invalid query, and an error will be thrown for illegal SQL syntax.

Caution must be exercised when developing dynamic query service.