With Invantive UniversalSQL, 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, such as for json auto, root('rootelement').
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 UniversalSQL 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 UniversalSQL 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
)