Anhängen oder Anhängen eines JSON-Objekts in ein anderes JSON-Objekt JSON-Objekt mit plsql

Question

Helo Leute, ich habe ein Problem, das ich lösen muss: Wie kann ich einige JSON-Objekte an ein anderes JSON-Array-Objekt anhängen?

Nach diesem Beispiel:

create table departments_json (
  department_id   integer not null primary key,
  department_data blob not null
);

alter table departments_json
add constraint dept_data_json 
check ( department_data is json );

insert into departments_json 
json values ( 110, utl_raw.cast_to_raw ( '{
  "department": "Accounting",
  "employees": [
    {
      "name": "Higgins, Shelley",
      "job": "Accounting Manager",
      "hireDate": "2002-06-07T00:00:00"
    },
    {
      "name": "Gietz, William",
      "job": "Public Accountant",
      "hireDate": "2002-06-07T00:00:00"
    }
  ]
}' ));

select department_id, utl_raw.cast_to_varchar2(department_data)
from departments_json
where department_id = 110;

Ich schaff das:

Jetzt habe ich noch einen json:

{
  "employees": [
    {
      "name": "Chen, John",
      "job": "Accountant",
      "hireDate": "2005-09-28T00:00:00"
    },
    {
      "name": "Greenberg, Nancy",
      "job": "Finance Manager",
      "hireDate": "2002-08-17T00:00:00"
    },
    {
      "name": "Urman, Jose Manuel",
      "job": "Accountant",
      "hireDate": "2006-03-07T00:00:00"
    }
  ]
}

Und ich muss das neue 3-Objekt im ersten json-Objekt anhängen, um so etwas zu haben:

Kann mir bitte jemand dabei helfen? Ich verstehe nicht den richtigen Weg.

Ich versuche etwas mit diesem Tutorial LINK , aber nichts.

Answer 1

Wenn Sie die Beispieldaten haben (gespeichert als CLOB):

create table departments_json (
  department_id
    integer
    NOT NULL
    CONSTRAINT departments_json__id__pk PRIMARY KEY,
  department_data
    CLOB
    NOT NULL
    CONSTRAINT departments_json__data__chk CHECK ( department_data IS JSON )
);

insert into departments_json 
json values ( 110, '{
  "department": "Accounting",
  "employees": [
    {
      "name": "Higgins, Shelley",
      "job": "Accounting Manager",
      "hireDate": "2002-06-07T00:00:00"
    },
    {
      "name": "Gietz, William",
      "job": "Public Accountant",
      "hireDate": "2002-06-07T00:00:00"
    }
  ]
}'
);

Dann können Sie JSON_MERGEPATCHsie verbinden (wenn Sie zuerst die vorhandenen und neuen Werte aggregieren):

WITH employees ( json ) AS (
  SELECT j.json
  FROM   departments_json d
         CROSS APPLY JSON_TABLE(
           d.department_data,
           '$.employees[*]' COLUMNS ( json CLOB FORMAT JSON PATH '$'
           )
         ) j
  WHERE  d.department_id = 110
UNION ALL
  SELECT j.json
  FROM   JSON_TABLE(
           '{
  "employees": [
    {
      "name": "Chen, John",
      "job": "Accountant",
      "hireDate": "2005-09-28T00:00:00"
    },
    {
      "name": "Greenberg, Nancy",
      "job": "Finance Manager",
      "hireDate": "2002-08-17T00:00:00"
    },
    {
      "name": "Urman, Jose Manuel",
      "job": "Accountant",
      "hireDate": "2006-03-07T00:00:00"
    }
  ]
}',
           '$.employees[*]' COLUMNS ( json CLOB FORMAT JSON PATH '$'
           )
         ) j
)
SELECT JSON_MERGEPATCH(
         department_data,
         (
           SELECT JSON_OBJECT(
                    KEY 'employees'
                    VALUE JSON_ARRAYAGG( json FORMAT JSON RETURNING CLOB )
                    FORMAT JSON
                  )
           FROM   employees
         )
         RETURNING CLOB PRETTY
       ) AS merged
FROM   departments_json
WHERE  department_id = 110;

Welche Ausgänge:

MERGED
-----------------------------------------
{
  "department" : "Accounting",
  "employees" :   [
    {
      "name" : "Higgins, Shelley",
      "job" : "Accounting Manager",
      "hireDate" : "2002-06-07T00:00:00"
    },
    {
      "name" : "Gietz, William",
      "job" : "Public Accountant",
      "hireDate" : "2002-06-07T00:00:00"
    },
    {
      "name" : "Chen, John",
      "job" : "Accountant",
      "hireDate" : "2005-09-28T00:00:00"
    },
    {
      "name" : "Greenberg, Nancy",
      "job" : "Finance Manager",
      "hireDate" : "2002-08-17T00:00:00"
    },
    {
      "name" : "Urman, Jose Manuel",
      "job" : "Accountant",
      "hireDate" : "2006-03-07T00:00:00"
    }
  ]
}

db <> hier fummeln

---

Aktualisieren

Wenn Sie eine BLOBSpalte verwenden, können Sie genau denselben Code verwenden. Wenn Sie es in einer UPDATEoder INSERT-Anweisung verwenden möchten, benötigen Sie eine Möglichkeit, die CLOBAusgabe von JSON_MERGEPATCHin eine zu konvertieren BLOB. Nicht verwenden, UTL_RAW.CAST_TO_RAWda dies fehlschlägt, wenn die Länge des JSON größer als 4000 Zeichen ist. Stattdessen können Sie die Funktion verwenden:

CREATE FUNCTION clob_to_blob(
  value            IN CLOB,
  charset_id       IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID,
  error_on_warning IN NUMBER  DEFAULT 0
) RETURN BLOB
IS
  result       BLOB;
  dest_offset  INTEGER := 1;
  src_offset   INTEGER := 1;
  lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  warning      INTEGER;
  warning_msg  VARCHAR2(50);
BEGIN
  DBMS_LOB.CreateTemporary(
    lob_loc => result,
    cache   => TRUE
  );

  DBMS_LOB.CONVERTTOBLOB(
    dest_lob     => result,
    src_clob     => value,
    amount       => LENGTH( value ),
    dest_offset  => dest_offset,
    src_offset   => src_offset,
    blob_csid    => charset_id,
    lang_context => lang_context,
    warning      => warning
  );
  
  IF warning != DBMS_LOB.NO_WARNING THEN
    IF warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
      warning_msg := 'Warning: Inconvertible character.';
    ELSE
      warning_msg := 'Warning: (' || warning || ') during CLOB conversion.';
    END IF;
    
    IF error_on_warning = 0 THEN
      DBMS_OUTPUT.PUT_LINE( warning_msg );
    ELSE
      RAISE_APPLICATION_ERROR(
        -20567, -- random value between -20000 and -20999
        warning_msg
      );
    END IF;
  END IF;

  RETURN result;
END clob_to_blob;
/

Wenn Sie dann die Tabellen- und Beispieldaten haben:

create table departments_json (
  department_id
    integer
    NOT NULL
    CONSTRAINT departments_json__id__pk PRIMARY KEY,
  department_data
    BLOB
    NOT NULL
    CONSTRAINT departments_json__data__chk CHECK ( department_data IS JSON )
);

insert into departments_json 
json values (
  110,
  CLOB_TO_BLOB(
'{
  "department": "Accounting",
  "employees": [
    {
      "name": "Higgins, Shelley",
      "job": "Accounting Manager",
      "hireDate": "2002-06-07T00:00:00"
    },
    {
      "name": "Gietz, William",
      "job": "Public Accountant",
      "hireDate": "2002-06-07T00:00:00"
    }
  ]
}'
  )
);

Um die Spalte mit den zusätzlichen Werten zu aktualisieren, können Sie Folgendes verwenden:

UPDATE departments_json
SET department_data = CLOB_TO_BLOB( JSON_MERGEPATCH(
         department_data,
         (
           SELECT JSON_OBJECT(
                    KEY 'employees'
                    VALUE JSON_ARRAYAGG( json FORMAT JSON RETURNING CLOB )
                    FORMAT JSON
                  )
           FROM   (
  SELECT j.json
  FROM   departments_json d
         CROSS APPLY JSON_TABLE(
           d.department_data,
           '$.employees[*]' COLUMNS ( json CLOB FORMAT JSON PATH '$'
           )
         ) j
  WHERE  d.department_id = 110
UNION ALL
  SELECT j.json
  FROM   JSON_TABLE(
           '{
  "employees": [
    {
      "name": "Chen, John",
      "job": "Accountant",
      "hireDate": "2005-09-28T00:00:00"
    },
    {
      "name": "Greenberg, Nancy",
      "job": "Finance Manager",
      "hireDate": "2002-08-17T00:00:00"
    },
    {
      "name": "Urman, Jose Manuel",
      "job": "Accountant",
      "hireDate": "2006-03-07T00:00:00"
    }
  ]
}',
           '$.employees[*]' COLUMNS ( json CLOB FORMAT JSON PATH '$'
           )
         ) j
           )
         )
         RETURNING CLOB PRETTY
       ) )
WHERE  department_id = 110;

db <> hier fummeln