JSON genereren vanuit Invantive SQL

Met Invantive SQL kan JSON omgezet worden via een select op een jsontable, zoals bijvoorbeeld beschreven in Run queries on Shopify API.

De andere richting - namelijk het omzetten van resultaten uit een query in JSON - is ook mogelijk vanaf versie 20.1.417. De werking is vergelijkbaar met SQL Server, zodat developers en consultants die bekend zijn met Microsoft SQL Server hun bestaande kennis kunnen hergebruiken.

De for json clause

Het omzetten van de resultaten van een select SQL query naar JSON gebeurt door een for json clause toe te voegen aan het einde van de query. Deze for json clause zorgt er voor dat de query maar één kolom teruggeeft met in de waarde JSON die de volledige resultaten van het select-statement bevat. Het aantal rijen is afhankelijk van de hoeveelheid data.

Via extra mogelijkheden op de for json clause kan het uitvoerformaat gepreciseerd worden:

Het meestgebruikte (en standaard) JSON-uitvoerformaat is een array van objecten. Elk object stelt een rij voor en de JSON array de uitkomsten van de SQL query.

JSON uitvoer SQL voorbeeld met path

Met de volgende query op de dual voorbeeldtabel wordt JSON gegenereerd:

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

Het resultaat is:

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

Merk op dat de kolomalias dummy.string de structuur van de JSON bepaalt. De array heeft maar één JSON object omdat dual zelf ook maar één rij bevat.

JSON uitvoer voorbeeld root

Met de volgende query op basis van for json worden geen kolomaliassen ingesteld zodat alle gegevens platgeslagen per record opgenomen worden. De extra clause root zorgt er voor dat de resultaten opgenomen worden als een array van objecten onder root:

select *
from   dual@datadictionary
for    json auto
,      root

Na root kan tussen ronde haken een van root-afwijkende JSON property naam ingesteld worden.

Resultaat (met toegevoegde regelovergangen voor de leesbaarheid):

{"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 uitvoer voorbeeld NDJSON

In het volgende SQL select statement wordt de for json clause gebruikt om NDJSON te genereren dankzij de without_array_wrapper. NDJSON is een populair uitvoerformaat gelijkend op JSON. Anders dan JSON ontbeert NDJSON de blokhaken van de JSON array voor en achter de objecten en de comma als scheider. Daardoor leent NDJSON zich beter dan JSON voor oneindig streaming data: elke regelovergang start een nieuw 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

Deze query levert de volgende JSON op:

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

In de SQL query is bij de for json clause ook include_null_values opgenomen. De toevoeging van include_null_values zorgt er voor dat de waarde van de kolom d3 ook opgenomen is.

JSON uitvoer van webpagina’s op app-online

De JSON die als uitvoer uit een query met for json clause komt is eenvoudig terug te geven via een applicatiemodule op Invantive Cloud. Dat kan voor grootschalig gebruik ook via app-online. Vergeet in dat geval niet om het Content Type van de gegenereerde pagina in te stellen op application/json. Op deze wijze kunnen eenvoudig rapporten gemaakt worden die met enkele of meerdere queries JSON teruggeven voor verdere verwerking in andere pakketten of EDI.

Geheugenbeslag for json

De for json clause geeft één of meerdere rijen terug, afhankelijk van de hoeveelheid data.

Bij gebruik van listagg in Invantive SQL kunnen de gegevens samengevoegd worden, mits de JSON niet langer wordt dan 2 GB of te wel 1 miljard tekens (in UTF16). In dat geval dient de software die de Invantive SQL aanroept zelf in brokken de gegevens samen te voegen tot één grote tekst.

Select json kolom

De for json achter de query verandert het aantal kolommen die uit de query terugkomen. Alle kolommen van de query verdwijnen en worden vervangen door de kolom json die de uitkomst van een groepfunctie op de opgevraagde waarden is. Echter, dit gebeurt pas na het selecteren van de kolommen met select * of select COLUMNS.

Alhoewel het logisch is dat de for json gebeurt na het selecteren van de op te nemen kolommen, kan het verwarrend zijn. Een query zoals:

select json
from   dual@datadictionary
for    json auto

is not possible, since the json column is replacing the original columns after the selection. When you want absolutely want to specify the column name, please use a construct such as:

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