JSON-Daten generieren

Mit SQL-Funktionen können Sie formatierte JSON-Daten aus relationalen Tabellen generieren.

Wir werden mit relationalen Daten arbeiten, um JSON zu generieren. Wir beginnen mit den Daten in zwei Tabellen. Eine enthält grundlegende Mitarbeiterdaten. Die andere enthält Kontoinformationen. Sie werden wie hier gezeigt erstellt und gefüllt.


create table empdata (id int,
                      last_name varchar(10),
                      first_name varchar(10), 
                      office_number varchar(10),
                      work_phone varchar(20),
                      home_phone varchar(20));
 
create table emp_account (id int,
                          account varchar(20));                      
                      
insert into empdata values (901, 'Doe', 'John', 'E-334', '555-7242', '555-3762');
insert into emp_account values (901, '36232'), (901, '73263');

insert into empdata values (902, 'Pan', 'Peter', 'E-216', '555-8925', null);
insert into emp_account values (902, '76232'), (902, '72963');

insert into empdata values (903, 'Jones', 'Mary', 'E-739', '555-4311', '555-6312');

insert into empdata values (904, 'Smith', 'Sally', null, null, null);

Unser Ziel ist es, vier Teile der Mitarbeiterinformationen in JSON zu erstellen, die wie die folgenden JSON-Objekte aussehen, die als Eingabe für die JSON_TABLE-Beispiele verwendet werden.



{"id":901, "name" : { "first":"John", "last":"Doe" }, "office" : "E-334",   
      "phones" : [ { "type":"home", "number":"555-3762" }, 
                   { "type":"work", "number":"555-7242" } ],      
      "accounts" : [ { "number":"36232"}, { "number":"73263"}] } 

{"id":902, "name" : { "first":"Peter", "last":"Pan" }, "office" : "E-216", 
      "phones" : [ { "type":"work", "number":"555-8925" } ], 
      "accounts" : [ { "number":"76232"}, {"number":"72963"}] }

{"id":903, "name" : { "first":"Mary", "last":"Jones" }, "office" : "E-739", 
      "phones" : [ { "type":"home", "number":"555-6312" }, 
                   { "type":"work", "number":"555-4311" } ], }

{"id":904, "name" : { "first":"Sally", "last":"Smith" } }

JSON-Basisobjekt erstellen

Beginnen Sie mit einem einfachen Beispiel, um zu sehen, wie einfach es ist, ein JSON-Basisobjekt zu generieren.

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number) 
from empdata;

In diesem Beispiel wird die Skalarfunktion JSON_OBJECT zum Generieren eines JSON-Objekts verwendet. Sie definiert drei Schlüssel: Wert -Paare, die die Spalten ID, LAST_NAME und OFFICE_NUMBER aus der Tabelle EMPDATA für die Werte verwenden. Jeder Schlüsselname wird als Zeichenfolge genau so angegeben, wie er in der Ausgabe angezeigt wird. Das Ergebnis dieser Abfrage sind vier Zeilen, eine für jede Zeile in der Tabelle EMPDATA.

{"id":901,"name":"Doe","office":"E-334"}
{"id":902,"name":"Pan","office":"E-216"}
{"id":903,"name":"Jones","office":"E-739"}
{"id":904,"name":"Smith","office":null}

Dies ist ein guter Anfang für unser Endziel, aber vielleicht möchten wir den Wert office weglassen, wenn er null ist, anstatt ihn einzuschließen. Dies ist einfach, wenn Sie die Klausel ABSENT ON NULL verwenden. Dies gibt an, dass, wenn einer der Werte für das JSON-Objekt der Nullwert ist, das Nullpaar key: value nicht in das Ergebnis eingeschlossen werden soll.

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number
                    absent on null) 
from empdata;

Jetzt gibt es keinen office -Wert für 904.

{"id":901,"name":"Doe","office":"E-334"}
{"id":902,"name":"Pan","office":"E-216"}
{"id":903,"name":"Jones","office":"E-739"}
{"id":904,"name":"Smith"}

JSON-Objekte verschachteln

Als Nächstes wird das Verbundnamenobjekt eingebettet. Zuerst generieren wir es auf eigene Faust.

select json_object ('first' value first_name, 
                    'last' value last_name) 
from empdata;

Das Ergebnis dieser Abfrage lautet wie folgt:

{"first":"John","last":"Doe"}
{"first":"Peter","last":"Pan"}
{"first":"Mary","last":"Jones"}
{"first":"Sally","last":"Smith"}

Da das richtig aussieht, wird es in die Masterabfrage aufgenommen. Alles, was wir tun müssen, ist, dieses name JSON-Objekt in das äußere einzubetten.

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number  
                    absent on null) 
from empdata;

Das Ergebnis sieht nun wie folgt aus, mit einem name -Objekt, das im äußeren JSON-Objekt verschachtelt ist:

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334"}
{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216"}
{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739"}
{"id":904,"name":{"first":"Sally","last":"Smith"}}

JSON-Array erstellen

Als Nächstes werden wir die Telefonanordnung in Angriff nehmen. Dies muss ein JSON-Array und kein JSON-Objekt sein. Das bedeutet, dass die Skalarfunktion JSON_ARRAY verwendet wird. Um die Generierung von Array-Einträgen für nicht angegebene Telefonnummern zu vermeiden, wird eine Anweisung CASE verwendet. Wenn keine Telefonnummer vorhanden ist, wird ein leeres JSON-Array generiert. Da wir wissen, ob wir die privaten oder privaten Telefonnummern generieren, geben wir type -Werte als home und workan.

select json_array 
        (case when home_phone is not null then 
            json_object('type' value 'home', 
                        'number' value home_phone) end, 
         case when work_phone is not null then 
            json_object('type' value 'work', 
                        'number' value work_phone) end 
         )
from empdata;

Hier ist das Ergebnis. Die Ausgabe für jeden Eintrag wird über zwei Zeilen getrennt, um das vollständige Ergebnis anzuzeigen.

["{\"type\":\"home\",\"number\":\"555-3762\"}",
    "{\"type\":\"work\",\"number\":\"555-7242\"}"]

["{\"type\":\"work\",\"number\":\"555-8925\"}"]

["{\"type\":\"home\",\"number\":\"555-6312\"}",
    "{\"type\":\"work\",\"number\":\"555-4311\"}"]

[]

Dies ist ein wenig unerwartet. Warum sind all diese zusätzlichen \ Zeichen im Ergebnis? Dies zeigt den Unterschied zwischen der Verarbeitung normaler Zeichendaten und Zeichendaten, die bereits als JSON formatiert wurden. Wenn JSON_ARRAY (oder eine der JSON-Veröffentlichungsfunktionen) seine Argumente betrachtet, erkennt es, wenn das Argument das direkte Ergebnis einer anderen JSON-Funktion ist. Ist dies der Fall, wird die Zeichenfolge als bereits formatierte JSON-Daten interpretiert. Dies bedeutet, dass die Funktion keines der Sonderzeichen in der Zeichenfolge mit Escapezeichen maskiert. Wenn das Argument nicht das direkte Ergebnis einer JSON-Funktion ist, wird es als Nicht-JSON-Zeichendaten interpretiert und die Escapeverarbeitung für den Wert ausgeführt. Da in diesem Beispiel das JSON_OBJECT in einen CASE -Ausdruck eingebettet wurde, ist die Tatsache, dass die Zeichenfolge bereits formatiert war, nicht bekannt. Um diese unerwünschten Escapezeichenfolgen zu vermeiden, müssen Sie JSON_ARRAY explizit mitteilen, dass das Argument bereits JSON ist, indem Sie die Klausel FORMAT JSON verwenden.

Mit diesem Wissen versuchen wir es noch einmal.

select json_array 
  (case when home_phone is not null then 
      json_object('type' value 'home', 
                  'number' value home_phone) end 
      format json,
   case when work_phone is not null then 
      json_object('type' value 'work', 
                  'number' value work_phone) end 
      format json)  
from empdata;

Das Ergebnis lautet wie folgt:

[{"type":"home","number":"555-3762"},{"type":"work","number":"555-7242"}]
[{"type":"work","number":"555-8925"}]
[{"type":"home","number":"555-6312"},{"type":"work","number":"555-4311"}]
[]

Jetzt können diese Informationen als nächster Teil unseres größeren JSON-Objekts aufgenommen werden.

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json)
                     absent on null) 
from empdata;

Dies gibt Folgendes zurück:

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
     "phones":[{"type":"home","number":"555-3762"},{"type":"work","number":"555-7242"}]}

{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
     "phones":[{"type":"work","number":"555-8925"}]}

{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
     "phones":[{"type":"home","number":"555-6312"},{"type":"work","number":"555-4311"}]}

{"id":904,"name":{"first":"Sally","last":"Smith"},
     "phones":[]}

Array aus mehreren Datenzeilen erstellen

Wir sind fast fertig. Wir müssen die Kontoinformationen, die in einer separaten Tabelle gespeichert sind, in ein Array aufnehmen. Generieren Sie ein Array von Objekten, die Kontonummern enthalten.

select json_array(json_object('number' value account)) 
from emp_account;

Das Ergebnis dieser Abfrage lautet wie folgt:

[{"number":"36232"}]
[{"number":"73263"}]
[{"number":"76232"}]
[{"number":"72963"}]

Das generierte ein Array für jede Kontonummer. Was wir brauchen, ist ein Array für jeden ID -Wert. Dazu müssen alle number -JSON-Objekte für einen einzelnen ID -Wert in einem einzelnen Array zusammengefasst werden.

select json_arrayagg(json_object('number' value account)) 
from emp_account group by id;

JSON_ARRAYAGG ist eine Aggregatfunktion, die mit Datengruppen arbeitet. In diesem Fall erfolgt die Gruppierung in der Spalte ID . Jeder Account für einen ID generiert ein Objekt, dann werden alle diese Objekte in einem einzelnen Array zusammengefasst. Diese Abfrage gibt nur zwei Zeilen zurück, eine für jeden ID -Wert, nach dem genau gesucht wurde.

[{"number":"36232"},{"number":"73263"}]
[{"number":"76232"},{"number":"72963"}]

Dieser Teil kann dem hinzugefügt werden, was wir bisher haben, um das generierte JSON-Objekt zu vervollständigen. Um nur die Accounts aus der Tabelle EMP_ACCOUNT für die aktuelle ID zurückzugeben, ist eine WHERE-Klausel erforderlich. Beachten Sie auch, dass, da das Account-Array von einer Abfrage zurückgegeben wird, eine FORMAT JSON-Klausel hinzugefügt werden muss, damit das äußere JSON_OBJECT den Wert korrekt als JSON identifiziert, das bereits formatiert ist.

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json),
                    'accounts' value (select json_arrayagg(
                                                json_object('number' value account)) 
                                      from emp_account a 
                                      where a.id = e.id group by id) format json
                    absent on null) 
from empdata e;

Jetzt haben wir unser Endergebnis erreicht.

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
      "phones":[{"type":"home","number":"555-3762"},
                {"type":"work","number":"555-7242"}],
      "accounts":[{"number":"36232"},{"number":"73263"}]}

{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
      "phones":[{"type":"work","number":"555-8925"}],
      "accounts":[{"number":"76232"},{"number":"72963"}]}

{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
      "phones":[{"type":"home","number":"555-6312"},
                {"type":"work","number":"555-4311"}]}

{"id":904,"name":{"first":"Sally","last":"Smith"},"phones":[]}

JSON in einem Objekt kombinieren

Gehen wir einen Schritt weiter. Manchmal benötigen Sie ein einzelnes JSON-Dokument mit allen Informationen. Wir nehmen die einzelnen Ergebnisse und bündeln sie.


select json_object('employees' value json_arrayagg(

       json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json),
                    'accounts' value (select json_arrayagg(
                                                json_object('number' value account)) 
                                      from emp_account a 
                                      where a.id = e.id group by id) format json
                    absent on null))) 
from empdata e;

Zuerst schließen wir die Ergebnisse mit einem JSON_ARRAYAGG ein, um ein Array von Mitarbeiterinformationen zu erstellen. Anschließend wird das Array mit einem JSON_OBJECT eingeschlossen, sodass das Endergebnis ein einzelnes JSON-Objekt ist. Das Ergebnis ist jetzt ein JSON-Objekt.

{"employees":[
{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
      "phones":[{"type":"home","number":"555-3762"},
                {"type":"work","number":"555-7242"}],
      "accounts":[{"number":"36232"},{"number":"73263"}]},
{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
      "phones":[{"type":"work","number":"555-8925"}],
      "accounts":[{"number":"76232"},{"number":"72963"}]},
{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
      "phones":[{"type":"home","number":"555-6312"},
                {"type":"work","number":"555-4311"}]},
{"id":904,"name":{"first":"Sally","last":"Smith"},"phones":[]}
]}

Ergebnisdatentyp festlegen

Mit jeder Veröffentlichungsfunktion können Sie den Datentyp und das Format des Ergebnisses der Funktion angeben. Wenn Sie die Klausel RETURNING weglassen, ist das Ergebnis ein CLOB mit einer Länge von 2G und einer CCSID von 1208. Sie wird als FORMAT JSON formatiert. Wenn Sie ein anderes Ergebnis wie BSON in einer Spalte VARBINARY (2000) benötigen, fügen Sie die Klausel RETURNING wie folgt hinzu:

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number
                    returning varbinary(2000) format bson) 
from empdata;

Verwenden eines allgemeinen Tabellenausdrucks zum Aggregieren eindeutiger Werte

Angenommen, Sie möchten die Verkaufsinformationen für jeden Vertriebsbeauftragten aggregieren und jede Region auflisten, in der ein Verkauf getätigt wurde. In der Tabelle SALES gibt es einen Eintrag für jeden Verkauf, der gemacht wurde. Wir benötigen eine Liste der einzelnen Verkäufer und eine Liste der Regionen für jeden Verkäufer. Diese werden mithilfe eines allgemeinen Tabellenausdrucks für jeden generiert. Diese temporären Ergebnisse werden zum Generieren der JSON-Objekte verwendet, eines für jeden Vertriebsbeauftragten.

with sales_tmp(sales_person) as (
    select distinct(sales_person) from sales),
  region_tmp(region,sales_person) as ( 
    select distinct region, sales_person from sales)

  select json_object( key sales_person 
                      value (select JSON_ARRAYAGG(region order by region) 
                             from region_tmp r where r.sales_person = s.sales_person) 
                      format json)
FROM sales_tmp s order by sales_person;

Aus der Beispieltabelle werden die folgenden drei Zeilen zurückgegeben:

{"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}

Wenn Sie ein einzelnes JSON-Objekt mit allen Ergebnissen zurückgeben wollen, wird es mit der folgenden Abfrage mit JSON_OBJECTAGG für Sie generiert.

with sales_tmp(sales_person) as (
    select distinct(sales_person) from sales),
  region_tmp(region,sales_person) as ( 
    select distinct region, sales_person from sales)

  select json_objectagg( key sales_person 
                         value (select JSON_ARRAYAGG(region order by region) 
                               from region_tmp r where r.sales_person = s.sales_person) 
                         format json)
FROM sales_tmp s;

Gibt eine Zeile zurück, die ein JSON-Objekt enthält. Es ist hier in mehrere Zeilen unterteilt, damit es leicht zu lesen ist.

{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}