json data service

JSON Data Service

Version: 17.07

Supported Since: 17.07

What is JSON Data Service?

JSON Data Service is a utility that allows you to expose data available behind a JDBC-compliant interface (e.g. a database) as a simple JSON API. It serves as a convenience mechanism for cases where you simply want to expose a dataset via HTTP/JSON without going through the overhead of setting up a dedicated API endpoint or backend service hierarchy.

The service essentially provides a mapping from HTTP request URLs to parameterized relational (SQL) queries that will be executed against a preconfigured database to retrieve single or batch results. Queries can utilize both HTTP path parameters (e.g. /customer/{id:INTEGER}) and query parameters (e.g. /customer/search?name={name:VARCHAR}) from the inbound request. Currently the utility supports only HTTP GET requests. As such, in cases where required access control is provided by other application-level security (e.g. authentication with RBAC) mechanisms, this utility can be directly utilized as a provider of a JSON-based interface without any additional configuration overhead.

Results of the database query are converted to JSON format (either an array or a single object, depending on the type of the query) and set as the payload of the outbound message, and the status code is set as follows:

200

If the query was successful, and requested results could be queried from the database

400

The request is erroneous, either because the HTTP path does not correspond to a valid query mapping, or because one or more parameters required for the query are missing

404

If the request corresponds to a single-entity query (see the sample use case for an example) but a single matching entity could not be found

Since it depends on HTTP path and query parameters as inputs, this processing element can only be used along with a HTTP (or HTTPS) ingress connector.

In order to use this processing element, you must first select the Data Service dependency from the processor list when you are creating an empty Ultra project. If you have already created a project, you can add this dependency via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the Processors list, select the Data Service dependency.

json data service outport

Out Ports

Next

Message will be emitted from this port after the data service response has been set

On Exception

The message context will be sent to this outport if any exception occurred while executing the data service request

Parameters

Data Source *

Basic

A JDBC data source (javax.sql.DataSource) used for executing SQL queries corresponding to incoming requests

Query Map *

Basic

A mapping of HTTP request URLs (with path and query parameters) to SQL queries, following the general format /path/with/{path parameter 1}/and/{path parameter 2}/and?key1={query parameter 1}&key2={query parameter 2}. See the sample use case for an example configuration.

Sample Use Case

Consider a scenario where you wish to expose a set of tourist destinations (stored in a locations table in a SQL database tourist running on a system dbhost) via a JSON API:

/places/{id}

provides details of a single specific location

/places/search?name={name-fragment}

provides a list of locations having name-fragment in their names

/places/nearby?lat={latitude}&lon={longitude}&r={distance}

provides a list of locations within a radial distance of distance from the location (latitude,longitude)

A simple flow that can accomplish the above requirement is given below:

json data service flow

Here, the HTTP ingress connector accepts incoming requests on port 8280, with HTTP paths starting with /places:

Http port

8280

Service path

/places.+

As a prerequisite for the JSON data service processing element, a DataSource resource has to be defined in the project.xpml file. This sample uses an implementation of javax.sql.DataSource provided by Spring, specifying a MySQL database:

<x:resources>
    <x:resource id="ds-resource">
        <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="url" value="jdbc:mysql://dbhost:3306/tourist"/>
            <property name="username" value="username"/>
            <property name="password" value="password"/>
        </bean>
    </x:resource>
</x:resources>

The JSON data service processing element can now be configured to use ds-resource as the Data Source.

Assuming a locations table with the following schema under the tourist database:

CREATE TABLE locations(
  id INTEGER NOT NULL,
  name VARCHAR(20),
  description VARCHAR(100),
  lat FLOAT(6,3),  -- latitude --
  lon FLOAT(6,3)   -- longitude --
);

The Query Map configuration for our scenario would resemble the following:

/places/search?name={fragment:VARCHAR}

SELECT * FROM locations WHERE name LIKE CONCAT('%', :fragment, '%')

/places/nearby?lat={latitude:FLOAT}
&lon={longitude:FLOAT}&r={distance:FLOAT}

SELECT * FROM locations WHERE (lat-:latitude)*(lat-:latitude)
+ (lon-:longitude)*(lon-:longitude) < :distance*:distance

/places/{id:INTEGER}

SINGLE: SELECT * FROM locations WHERE id = :id

Since the URL pattern comparisons are done in sequence, the more specific patterns should be specified towards the top of the Query Map. In the above example, /places/search and /places/nearby are more specific than /places/{id} so that they have been included higher in the hierarchy.

We have prefixed the last query with SINGLE: to indicate that we want only a single result (i.e. a JSON object rather than a JSON array with a single entity) for the corresponding /places/{id} requests. This SINGLE: fragment will be interpreted by the processing element and will not be propagated to the final SQL query.

In addition to extracting a full path fragment or query parameter as above, the service also supports retrieval of a part of such a fragment as the SQL parameter value. For example, if in the above case, if the id value in the path segment of (1) is prefixed with "loc" by default, which should not propagate into the database query (e.g. /places/loc10 should trigger a lookup for location with loc_id = 10), the corresponding URL pattern can be updated as /places/loc{id:INTEGER}. This will ensure that, for each request, the first 3 characters ("loc") of the path parameter will be stripped and only the rest of the token will be utilized as the SQL query parameter.

In this topic
In this topic
Contact Us