Generate JSON from Invantive SQL queries

Go to Dutch version

With Invantive SQL, JSON can be converted via a select on a jsontable, as described for example in Run queries on Shopify API.

The other direction - namely converting results from a query into JSON - is also possible as of version 20.1.417. The operation is similar to SQL Server, so developers and consultants familiar with Microsoft SQL Server can reuse their existing knowledge.

The for json clause

Converting the results of a select SQL query to JSON is done by adding a for json clause to the end of the query. This for json clause ensures that the query returns only one column with the JSON value containing the entire results of the select statement. The number of rows depends on the amount of data and is paged (standard per 1.000, configurable through output per ... rows)

Via extra options on the for json clause the output format can be specified:

The most common (and standard) JSON output format is an array of objects. Each object represents a row and the JSON array the results of the SQL query.

JSON output SQL example with path.

The following query on the dual example table generates JSON:

select dummy_string [dummy.string]
from   dual@datadictionary
for    json path

The result is:

[{"dummy":{"string":"A"}}]

Note that the column alias dummy.string defines the structure of the JSON. The array has only one JSON object because dual itself contains only one row.

JSON output example root

With the following query based on for json, no column aliases are set so all data is recorded flat per record. The additional clause root ensures that the results are recorded as an array of objects under root:

select *
from   dual@datadictionary
for    json auto
,      root

After root, a JSON property name different from root can be set in round brackets.

Result (with added line transitions for readability):

{"root":[{"dummy_string":"A"
, "dummy_int16":24
, "dummy_int32":25
, "dummy_int64":26
,"dummy_datetime":"2015-06-08T14:57:19"
,"dummy_date":"2015-06-08T00:00:00"
,"dummy_double":123.4
, "dummy_float":124.5
,"dummy_decimal":194.79
,"dummy_guid":"faec610c-eb12-4fa8-80a7-ad8e2e1586ff"
, "dummy_blob": "iVBOR.....="
,"dummy_string_int64":"26"
, "dummy_bool":true
,"dummy_double_above":1065.5712340000005
,"dummy_double_below":1065.57120009996
, "dummy_char": "A"
,"dummy_uint16":65535
,"dummy_uint32":4294967295
,"dummy_uint64":18446744073709551615}]}

JSON output example NDJSON

In the following SQL select statement, the for json clause is used to generate NDJSON thanks to the without_array_wrapper. NDJSON is a popular output format similar to JSON. Unlike JSON, NDJSON lacks the block brackets of the JSON array before and after the objects and the comma as separator. As a result, NDJSON lends itself better than JSON to infinite streaming of data: each line transition starts a new object.

select dmy1.dummy_string d1
,      dmy2.dummy_string d2
,      dmy2.dummy_string_null d3
from   dual@datadictionary dmy1
join   dual@datadictionary dmy2
on     dmy1.dummy_string = dmy2.dummy_string
for    json auto
,      include_null_values
,      without_array_wrapper

This query returns the following JSON:

{\"d1\":\"A\",\"d2\":\"A\",\"d3\":null}

In the SQL query, the for json clause also includes include_null_values. The addition of include_null_values ensures that the value of the column d3 is also included.

JSON output from web pages on app-online

The JSON that comes as output from a query with for json clause is easy to return through an application module on Invantive Cloud. For large scale use this can also be done via app-online. In that case don’t forget to set the Content Type of the generated page to application/json. In this way, reports can easily be created that return JSON with a few or more queries for further processing in other packages or EDI.

Memory requirements for json.

The for json clause returns one or more rows, depending on the amount of data.

When using listagg in Invantive SQL the data can be merged, provided that the JSON does not exceed 2 GB or 1 billion characters (in UTF16). In that case the software that calls the Invantive SQL needs to merge the data into one big text.

Select json column

The for json after the query changes the number of columns returned from the query. All the columns from the query disappear and are replaced by the json column which is the result of a group function on the values retrieved. However, this only happens after selecting the columns with select * or select COLUMNS.

Although it makes sense that the for json happens after selecting the columns to be included, it can be confusing. A query such as:

select json
from   dual@datadictionary
for    json auto

is not possible because the json column replaces the original columns after selection. If you absolutely want to specify the column name, use a construction such as:

select json
from   ( select *
         from dual@datadictionary
         for    json auto
       )