Append or attach a json object inside another json object json object with plsql

Question

Helo guys, i have a problem that i need to solve: How can i attach some json objects into another json array object ?

Following this example:

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;

I got this:

Now i have this another 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"
    }
  ]
}

And i need attach, the new 3 object inside the first json object, to have something like this:

Can somebody help with this, please? i don't get the right way.

i try something using this tutorial LINK, but nothing.

Why are you sorting JSON (character data) in a BLOB data type (binary data) and why cast to and from a RAW data type? Why don't you just use a CLOB? — MT0
– MT0, Commented Oct 15, 2020 at 10:45
that is not the problem, is how the table was defined. the problem is how append new json objects inside the first one. Regards — Julio
– Julio, Commented Oct 15, 2020 at 10:51

MT0 · Accepted Answer · 2020-10-16 00:15:23Z

2

If you have the sample data (stored as 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"
    }
  ]
}'
);

Then you can use JSON_MERGEPATCH to join them (if you aggregate the existing and new values first):

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;

Which outputs:

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<>fiddle here

Update

If you are using a BLOB column then you can use exactly the same code. If you want to use it in an UPDATE or INSERT statement then you will need a way to convert the CLOB output from JSON_MERGEPATCH to a BLOB. Do not use UTL_RAW.CAST_TO_RAW as it will fail if the length of the JSON is greater than 4000 character; instead you can use the function:

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

Then, if you have the table and sample data:

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

Then, to update the column with the additional values, you can use:

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<>fiddle here

edited Oct 16, 2020 at 0:15

answered Oct 15, 2020 at 11:20

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Julio Over a year ago

thanks, but one point, why did you recreate the table, the column type can't be modify, for this example i create the table manually , but was based on a the original table, that use BLOB. Is't possible adapt these response to a BLOB column?. Regards

MT0 Over a year ago

@Julio Also, UTL_RAW.CAST_TO_VARCHAR2 requires the input to be less than 4000 characters; this breaks the query quite quickly when you add more items to the JSON array and the string becomes too long. db<>fiddle If you use a CLOB then you bypass this conversion and can store very large pieces of JSON.

Julio Over a year ago

i used utl_raw.cast_to_varchar2(department_data) just to see if my insert works, is not something special. the point is now, how i suggest change the column type, from BLOB to CLOB. Also i was following this tutorial to create/replicate the source table blogs.oracle.com/sql/… and this guy use BLOB

MT0 Over a year ago

While I see the point they are trying to make in that blog about character sets, the unfortunate side-effect of their method is that UTL_RAW.CAST_TO_RAW and UTL_RAW.CAST_TO_VARCHAR2, respectively, take and return VARCHAR2 data types and if you have a JSON value greater than 4000 characters then they can fail (as I demonstrated in the db<>fiddle in the previous comment). If you are not worried about character set issues, just use CLOB as it is much simpler.

MT0 Over a year ago

@Julio Updated with a function to convert CLOB_TO_BLOB that does not use UTL_RAW.CAST_TO_RAW and then all you need to do for UPDATE (or INSERT) statements is to wrap the output from JSON_MERGEPATCH to convert from its CLOB output to a BLOB.

|

Collectives™ on Stack Overflow

Append or attach a json object inside another json object json object with plsql

1 Answer 1

Update

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related