Preparation
Let’s create our table
CREATE TABLE tbl_json_example ( id NUMBER PRIMARY KEY, st_character CLOB, st_ship CLOB );
and add some data to it
INSERT INTO tbl_json_example (id, st_character, st_ship) VALUES ( 1, '{"character": "Captain Jean-Luc Picard", "rank": "Captain"}', '{"ship": "USS Enterprise-D", "registry": "NCC-1701-D", "class": "Galaxy-class", "species": "Human"}' ); INSERT INTO tbl_json_example (id, st_character, st_ship) VALUES ( 2, '{"character": "Commander Spock", "rank": "Commander"}', '{"ship": "USS Enterprise", "registry": "NCC-1701", "class": "Constitution-class", "species": "Vulcan"}' ); INSERT INTO tbl_json_example (id, st_character, st_ship) VALUES ( 3, '{"character": "Captain Kathryn Janeway", "rank": "Captain"}', '{"ship": "USS Voyager", "registry": "NCC-74656", "class": "Intrepid-class", "species": "Human"}' );
Now le’ts have a look if everything is fine so far
Our goal is now to move the attribute „species“ from the ship column into the character column.
Solution with JSON_MERGEPATCH
Oracle 19c provides a function called JSON_MERGEPATCH to update specific portions of a JSON. So let’s have a closer look to see if this is what we’re looking for
SELECT json_mergepatch(st_character, '{"species": "SOME_VALUE"}') from tbl_json_example;
Looks fine so far
Now let’s extend the whole thing so the attribute „species“ is read from the ST_SHIP column and added to the ST_CHARACTER column and put it into an UPDATE statement
UPDATE tbl_json_example SET st_character = json_mergepatch(st_character, '{"species": "' || json_value(st_ship, '$.species') || '"}');
It works, nice!
Since we don’t want the attribute in our ST_SHIP column, let’s delete it. Again, first with a SELECT statement
SELECT json_mergepatch(st_ship, '{"species": null}') from tbl_json_example;
Good
Now for the DELETE statement
UPDATE tbl_json_example SET st_ship = json_mergepatch(st_ship, '{"species": null}');
Final result
Be careful, if your target colum already has JSON attributes, that you want to merge. The function will override them.
Alternative with JSON_TRANSFORM
Since JSON_MERGEPATCH is only available from Oracle 19c on, there is an alternative for the 12c users (sorry if you use older versions)
For insertion
SELECT JSON_TRANSFORM(st_character, SET '$.species' = json_value(st_ship, '$.species')) from tbl_json_example;
and deletion
SELECT JSON_TRANSFORM(st_ship, REMOVE '$.species') from tbl_json_example;
Sources:
https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/oracle-sql-function-json_transform.html#GUID-7BED994B-EAA3-4FF0-824D-C12ADAB862C1
https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/oracle-sql-function-json_mergepatch.html#GUID-80B4DA1C-246F-4AB4-8DF5-D492E5661AA8
Der Beitrag Move JSON attributes in Oracle with JSON_MERGEPATCH erschien zuerst auf Business -Software- und IT-Blog - Wir gestalten digitale Wertschöpfung.
This post first appeared on DoubleSlash IT Business Und Software, please read the originial post: here