1.39 SQL views

The SQL views resource allows you to create and retrieve the result set of SQL views. The SQL views can be executed directly against the database and render the result set through the Web API resource.

/api/26/sqlViews

SQL views are useful for creating data views which may be more easily constructed with SQL compared combining the multiple objects of the Web API. As an example, lets assume we have been asked to provide a view of all organization units with their names, parent names, organization unit level and name, and the coordinates listed in the database. The view might look something like this:

SELECT ou.name as orgunit, par.name as parent, ou.coordinates, ous.level, oul.name from organisationunit ou
INNER JOIN _orgunitstructure ous ON ou.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid
INNER JOIN orgunitlevel oul ON ous.level = oul.level
WHERE ou.coordinates is not null
ORDER BY oul.level, par.name, ou.name

We will use curl to first execute the view on the DHIS2 server. This is essentially a materialization process, and ensures that we have the most recent data available through the SQL view when it is retrieved from the server. You can first look up the SQL view from the api/sqlViews resource, then POST using the following command:

curl "https://play.dhis2.org/demo/api/26/sqlViews/dI68mLkP1wN/execute" -X POST -u admin:district -v

The next step in the process is the retrieval of the data.The basic structure of the URL is as follows

http://{server}/api/26/sqlViews/{id}/data(.csv)

The {server} parameter should be replaced with your own server. The next part of the URL /api/sqlViews/ should be appended with the specific SQL view identifier. Append either data for XML data or data.csv for comma delimited values. Support response formats are json, xml, csv, xls, html and html+css. As an example, the following command would retrieve XML data for the SQL view defined above.

curl "https://play.dhis2.org/demo/api/26/sqlViews/dI68mLkP1wN/data.csv" -u admin:district -v

There are three types of SQL views:

1.39.1 Criteria

You can do simple filtering on the columns in the result set by appending criteria query parameters to the URL, using the column names and filter values separated by columns as parameter values, on the following format:

/api/26/sqlViews/{id}/data?criteria=col1:value1&criteria=col2:value2

As an example, to filter the SQL view result set above to only return organisation units at level 4 you can use the following URL:

https://play.dhis2.org/demo/api/26/sqlViews/dI68mLkP1wN/data.csv?criteria=level:4

1.39.2 Variables

SQL views support variable substitution. Variable substitution is only available for SQL view of type query , meaning SQL views which are not created in the database but simply executed as regular SQL queries. Variables can be inserted directly into the SQL query and must be on this format:

${variable-key}

As an example, an SQL query that retrieves all data elements of a given value type where the value type is defined through a variable can look like this:

select * from dataelement where valuetype = '${valueType}';

These variables can then be supplied as part of the URL when requested through the sqlViews Web API resource. Variables can be supplied on the following format:

/api/sqlViews/{id}/data?var=key1:value1&var=key2:value2

An example query corresponding to the example above can look like this:

/api/26/sqlViews/dI68mLkP1wN/data.json?var=valueType:int

The valueType variable will be substituted with the int value, and the query will return data elements with int value type.

The variable parameter must contain alphanumeric characters only. The variables must contain alphanumeric, dash, underscore and whitespace characters only.

1.39.3 Filtering

The SQL view api supports data filtering, equal to the metadata object filter . For a complete list of filter operators you can look at the documentation for metadata object filter .

To use filters, simply add them as parameters at the end of the request url for your SQL view like this:

/api/sqlViews/w3UxFykyHFy/data.json?filter=orgunit_level:eq:2&filter=orgunit_name:ilike:bo

This request will return a result including org units with “bo” in the name and which has org unit level 2.

The following example will return all org units with orgunit_level 2 or 4:

/api/sqlViews/w3UxFykyHFy/data.json?filter=orgunit_level:in:[2,4]

And last, an example to return all org units that does not start with “Bo”

/api/sqlViews/w3UxFykyHFy/data.json?filter=orgunit_name:!like:Bo