JSON Update in 19c mit SQL Funktion JSON_MERGEPATCH

Seit Oracle 12.1 wird JSON in der Datenbank unterstützt d.h. es gibt die Möglichkeit auf JSON-Daten mit Standard Datenbankmitteln zuzugreifen. Die Idee dahinter ist, nicht nur einen einfachen Textstring zu speichern und auf diesen zuzugreifen, sondern auch spezielle JSON Pfad Zugriffe oder JSON Validierungen zu ermöglichen, um nur einige Features zu nennen. Zusätzlich stehen alle relationalen Datenbank Features bei der Nutzung von JSON zur Verfügung, wie z.B. Indizes, Transaktionshandling, gemischte Abfragen, relationale Views, External Tables usw. Zusätzlich dazu gibt es Funktionen zum Extrahieren von JSON, aber auch zum Generieren von JSON Daten, zum Suchen im JSON-Dokument und zum Laden von Daten. Benötigt man eine Einführung in das Thema, kann man folgende deutsche Textbeiträge lesen:

Auch in 19c sind einige interessante Erweiterungen im Bereich JSON ergänzt worden, wie zum Beispiel die Möglichkeit JSON-Dokumente mit einem Kommando deklarativ zu aktualisieren. Dazu ist die neue SQL-Funktion JSON_MERGEPATCH eingeführt worden.

Wie hat man eigentlich bisher ein Update auf ein JSON-Dokument durchgeführt? Seit 12.2 gibt es eine spezielle PL/SQL API mit speziellen Objekttypen und ihren zugehörigen Funktionen, die Zugriff auf JSON Dokumente in der Datenbank gewährleisten. Damit ist es möglich Key:Value-Paare hinzuzufügen, zu verändern oder zu löschen. Man kann durch ein JSON-Dokument navigieren - vergleichbar mit dem XML Document Object Model (DOM). Ein Beispiel für solch ein PL/SQL Programm zum Verändern von Daten in einem JSON-Dokument findet sich im Tipp JSON in 12.2: JSON Generierung, neues Data Guide Konzept, neue Objekttypen. Der Programmcode ist in folgendem Code Ausschnitt zu lesen. Die Aufgabe bestand darin, das Key:Value-Paar "User":"CJOHNSON" in "User":"USCHWINN" zu ändern.

declare
     cursor getDocuments 
     is
     select JSON_DOCUMENT
     from JSON_TAB j
     where json_value(json_document,'$.User') = 'CJOHNSON' for UPDATE;   
     V_RESULT            JSON_ELEMENT_T;    
     V_DOCUMENT_OBJECT   JSON_OBJECT_T;
     V_NEW_DOCUMENT      VARCHAR2(4000);
begin
    for doc in getdocuments loop 
--  setup of the internal representation     
    V_RESULT := JSON_ELEMENT_T.parse(doc.json_DOCUMENT);
    V_DOCUMENT_OBJECT := treat(V_RESULT as JSON_OBJECT_T);
 
    if (V_Document_OBJECT.get_String('User') = 'CJOHNSON') then
       V_document_object.put('User','USCHWINN');
       end if;
-- transformation to a string 
   V_NEW_DOCUMENT := V_DOCUMENT_OBJECT.to_string();

-- update operation
    update %TABLE_NAME%
       set JSON_DOCUMENT = V_NEW_DOCUMENT
     where current of getDocuments;
  commit;
end loop;
end;
/

Welche Funktionen besitzt nun die neue SQL-Funktion JSON_MERGEPATCH? Mit JSON_MERGEPATCH lassen sich gemäß IETF-Standard bestimmte Teile eines JSON-Dokuments aktualisieren. Wie der Name schon andeutet, legt man in einer Art Patch-Dokument genau fest, wie die Änderungen im Source-Dokument aussehen sollen. JSON_MERGEPATCH führt dann anschließend die Informationen aus dem Patch- und dem Source-Dokument zusammmen.

Demonstrieren wir im Folgenden die Funktionen an ein paar Beispielen. Dazu verwenden wir die Tabelle JSON_TAB aus dem Tipp JSON in 12.2: JSON Generierung, neues Data Guide Konzept, neue Objekttypen. Die Tabelle JSON_TAB hat dabei folgendes Aussehen.

SQL> desc json_TAB
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 JSON_DOCUMENT                                      CLOB

SQL> select * from json_tab 
     where rownum=1 and JSON_VALUE(json_document, '$.User') = 'CJOHNSON';

        ID
----------
JSON_DOCUMENT
------------------------------------------------------------------------------------------------------
      9273
{"PONumber":331,"Reference":"CJOHNSON-20141130","Requestor":"Charles Johnson","User":"CJOHNSON","CostC
enter":"A40","ShippingInstructions":{"name":"Charles Johnson","Address":{"street":"Magdalen Centre, Th
e Isis Science Park","city":"Oxford","county":"Oxon.","postcode":"OX9 9ZB","country":"United Kingdom"}
,"Phone":[{"type":"Office","number":"430-555-1898"}]},"Special Instructions":"COD","LineItems":[{"Item
Number":1,"Part":{"Description":"Civil War Combat: America's Bloodiest","UnitPrice":27.95,"UPCCode":73
3961701142},"Quantity":6.0},{"ItemNumber":2,"Part":{"Description":"Adventures Of The Old West","UnitPr
ice":19.95,"UPCCode":56775038991},"Quantity":6.0},{"ItemNumber":3,"Part":{"Description":"Pat Metheny:
Imaginary Day","UnitPrice":27.95,"UPCCode":13023157491},"Quantity":6.0},{"ItemNumber":4,"Part":{"Descr
iption":"Malcolm X","UnitPrice":19.95,"UPCCode":85391259626},"Quantity":9.0},{"ItemNumber":5,"Part":{"
Description":"Everyone Says I Love You","UnitPrice":19.95,"UPCCode":717951003225},"Quantity":7.0}]}

Nun wollen wir ein Update auf das JSON-Dokument durchführen und den Wert des Keys "User" von "CJOHNSON" in "USCHWINN" (siehe PL/SQL Beispiel oben) und den Key "CostCenter" in "XX" ändern. Statt eines PL/SQL Programms soll das Ganze mit der neuen SQL-Funktion JSON_MERGEPATCH durchgeführt werden. JSON_MERGEPATCH kann auch in einer SELECT-Liste verwendet werden. Die Klausel PRETTY ASCII erzeugt dabei eine bessere Formatierung um die Lesbarkeit zu erhöhen.

SQL> select json_mergepatch(json_document,'{"User":"USCHWINN", "CostCenter":"XX"}' pretty ascii)
     from JSON_TAB 
     where JSON_VALUE(json_document, '$.User')='CJOHNSON' and rownum=1; 

JSON_MERGEPATCH(JSON_DOCUMENT,'{"USER":"USCHWINN","COSTCENTER":"XX"}'PRETTYASCII)
------------------------------------------------------------------------------------------------------
{
  "PONumber" : 331,
  "Reference" : "CJOHNSON-20141130",
  "Requestor" : "Charles Johnson",
  "User" : "USCHWINN",
  "CostCenter" : "XX",
  "ShippingInstructions" :
  {
...

Das zugehörige UPDATE Statement würde so aussehen:

SQL> update json_tab set json_document = json_mergepatch(json_document,'{"User":"USCHWINN", "CostCenter":"XX"}') 
     where JSON_VALUE(json_document, '$.User')='CJOHNSON';

69 rows updated.

Wird im Patch Dokument der Wert null für einen Value verwendet, wird der zugehörige Key (hier "User") gelöscht.

SQL> select json_mergepatch(json_document,'{"User":null, "CostCenter":"XX"}' pretty ascii)
     from JSON_TAB 
     where JSON_VALUE(json_document, '$.User')='CJOHNSON' and rownum=1;
 
JSON_MERGEPATCH(JSON_DOCUMENT,'{"USER":NULL,"COSTCENTER":"XX"}'PRETTYASCII)
--------------------------------------------------------------------------------
{
  "PONumber" : 331,
  "Reference" : "CJOHNSON-20141130",
  "Requestor" : "Charles Johnson",
  "CostCenter" : "XX",
  "ShippingInstructions" :
  {
   ...

Wird im Patch-Dokument ein neues Key:Value-Paar angegeben, wird dieses Key:Value-Paar ergänzt. Das folgende Statement ergänzt das Key:Value-Paar "NewUser":"Ulrike".

SQL> select json_mergepatch(json_document,'{"NewUser":"Ulrike", "CostCenter":"XX"}' pretty ascii)
     from JSON_TAB 
     where JSON_VALUE(json_document, '$.User')='CJOHNSON' and rownum=1;

JSON_MERGEPATCH(JSON_DOCUMENT,'{"NEWUSER":"ULRIKE","COSTCENTER":"XX"}'PRETTYASCI
--------------------------------------------------------------------------------
{
  "PONumber" : 331,
  "Reference" : "CJOHNSON-20141130",
  "Requestor" : "Charles Johnson",
  "User" : "CJOHNSON",
  "CostCenter" : "XX",
  "ShippingInstructions" :
  {
    "name" : "Charles Johnson",
    "Address" :
    {
      "street" : "Magdalen Centre, The Isis Science Park",
      "city" : "Oxford",
      "county" : "Oxon.",
      "postcode" : "OX9 9ZB",
      "country" : "United Kingdom"
    },
    "Phone" :
    [
      {
        "type" : "Office",
        "number" : "430-555-1898"
      }
    ]
  },
  "Special Instructions" : "COD",
  "LineItems" :
  [
    {
      "ItemNumber" : 1,
      "Part" :
      {
        "Description" : "Civil War Combat: America's Bloodiest",
        "UnitPrice" : 27.95,
        "UPCCode" : 733961701142
      },
      "Quantity" : 6
    },
    {
      "ItemNumber" : 2,
      "Part" :
      {
        "Description" : "Adventures Of The Old West",
        "UnitPrice" : 19.95,
        "UPCCode" : 56775038991
      },
      "Quantity" : 6
    },
    {
      "ItemNumber" : 3,
      "Part" :
      {
        "Description" : "Pat Metheny: Imaginary Day",
        "UnitPrice" : 27.95,
        "UPCCode" : 13023157491
      },
      "Quantity" : 6
    },
    {
      "ItemNumber" : 4,
      "Part" :
      {
        "Description" : "Malcolm X",
        "UnitPrice" : 19.95,
        "UPCCode" : 85391259626
      },
      "Quantity" : 9
    },
    {
      "ItemNumber" : 5,
      "Part" :
      {
        "Description" : "Everyone Says I Love You",
        "UnitPrice" : 19.95,
        "UPCCode" : 717951003225
      },
      "Quantity" : 7
    }
  ],
  "NewUser" : "Ulrike"
}

Ein Patch-Element mit dem Wert null wird ignoriert, wenn das Source Dokument kein solches Feld besitzt. Folgendes Beispiel veranschaulicht dies.

SQL> select json_mergepatch(json_document,'{"NewUser":null, "CostCenter":"XX"}' pretty ascii)
     from JSON_TAB 
     where JSON_VALUE(json_document, '$.User')='CJOHNSON' and rownum=1

JSON_MERGEPATCH(JSON_DOCUMENT,'{"NEWUSER":NULL,"COSTCENTER":"XX"}'PRETTYASCII)
--------------------------------------------------------------------------------
{
  "PONumber" : 331,
  "Reference" : "CJOHNSON-20141130",
  "Requestor" : "Charles Johnson",
  "User" : "CJOHNSON",
  "CostCenter" : "XX",
  "ShippingInstructions" :
  {
    "name" : "Charles Johnson",
    "Address" :
    {
      "street" : "Magdalen Centre, The Isis Science Park",
      "city" : "Oxford",
      "county" : "Oxon.",
      "postcode" : "OX9 9ZB",
      "country" : "United Kingdom"
    },
    "Phone" :
    [
      {
        "type" : "Office",
        "number" : "430-555-1898"
      }
    ]
  },
  "Special Instructions" : "COD",
  "LineItems" :
  [
    {
      "ItemNumber" : 1,
      "Part" :
      {
        "Description" : "Civil War Combat: America's Bloodiest",
        "UnitPrice" : 27.95,
        "UPCCode" : 733961701142
      },
      "Quantity" : 6
    },
    {
      "ItemNumber" : 2,
      "Part" :
      {
        "Description" : "Adventures Of The Old West",
        "UnitPrice" : 19.95,
        "UPCCode" : 56775038991
      },
      "Quantity" : 6
    },
    {
      "ItemNumber" : 3,
      "Part" :
      {
        "Description" : "Pat Metheny: Imaginary Day",
        "UnitPrice" : 27.95,
        "UPCCode" : 13023157491
      },
      "Quantity" : 6
    },
    {
      "ItemNumber" : 4,
      "Part" :
      {
        "Description" : "Malcolm X",
        "UnitPrice" : 19.95,
        "UPCCode" : 85391259626
      },
      "Quantity" : 9
    },
    {
      "ItemNumber" : 5,
      "Part" :
      {
        "Description" : "Everyone Says I Love You",
        "UnitPrice" : 19.95,
        "UPCCode" : 717951003225
      },
      "Quantity" : 7
    }
  ]
}

Weitere Informationen zum Thema findet man in den Handbüchern und unter folgenden Links.

Weitere Informationen

 

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services