Agregar o adjuntar un objeto json dentro de otro objeto json objeto json con plsql

Question

Hola chicos, tengo un problema que necesito resolver: ¿cómo puedo adjuntar algunos objetos json en otro objeto de matriz json?

Siguiendo este ejemplo:

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;

Tengo esto:

Ahora tengo este otro 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"
    }
  ]
}

Y necesito adjuntar, el nuevo objeto 3 dentro del primer objeto json, para tener algo como esto:

¿Alguien puede ayudar con esto, por favor? No entiendo de la manera correcta.

Intento algo usando este tutorial LINK , pero nada.

Answer 1

Si tiene los datos de muestra (almacenados como a 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"
    }
  ]
}'
);

Luego, puede usar JSON_MERGEPATCHpara unirlos (si agrega primero los valores existentes y nuevos):

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;

Qué salidas:

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 <> violín aquí

---

Actualizar

Si está utilizando una BLOBcolumna, puede utilizar exactamente el mismo código. Si desea usarlo en una declaración UPDATEo INSERT, necesitará una forma de convertir la CLOBsalida de JSON_MERGEPATCHa BLOB. No lo utilice, UTL_RAW.CAST_TO_RAWya que fallará si la longitud del JSON es superior a 4000 caracteres; en su lugar, puede usar la función:

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;
/

Entonces, si tiene la tabla y los datos de muestra:

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"
    }
  ]
}'
  )
);

Luego, para actualizar la columna con los valores adicionales, puede usar:

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 <> violín aquí