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.
If you have the sample data (stored as a
CLOB
):Then you can use
JSON_MERGEPATCH
to join them (if you aggregate the existing and new values first):Which outputs:
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 anUPDATE
orINSERT
statement then you will need a way to convert theCLOB
output fromJSON_MERGEPATCH
to aBLOB
. Do not useUTL_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:Then, if you have the table and sample data:
Then, to update the column with the additional values, you can use:
db<>fiddle here