Met Invantive UniversalSQL 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 en wordt gepagineerd (standaard per 1.000, instelbaar via output per ... rows).
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, zoals for json auto, root('rootelement').
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 UniversalSQL 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 UniversalSQL 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 niet mogelijk, omdat de json kolom de originele kolommen vervangt na de selectie. Wanneer u absoluut de kolomnaam wilt specificeren, gebruik dan een constructie zoals:
select json
from ( select *
from dual@datadictionary
for json auto
)