CODEWITHSUNDEEP
jsonoracle-database
Ronald Burton
Ronald Burton

Reputation: 15

How do I return a JSON updated document in Oracle?

From the docs I see an example:

SELECT json_mergepatch(po_document, '{"Special Instructions":null}'
                       RETURNING CLOB PRETTY)
  FROM j_purchaseorder;

But When I try this code in SQL Developer I get a squiggly line under CLOB and an error when I run the query?

Upvotes: 0

Views: 81

Answers (1)

MT0
MT0

Reputation: 168001

It works in Oracle 18c:

SELECT json_mergepatch(
         po_document,
         '{"Special Instructions":null}'
         RETURNING CLOB PRETTY
       ) AS updated_po_document
FROM   j_purchaseorder;

Which for the test data:

CREATE TABLE j_purchaseorder( po_document CLOB CHECK ( po_document IS JSON ) );

INSERT INTO j_purchaseorder ( po_document )
  VALUES ( '{"existing":"value", "Special Instructions": 42}' );

Outputs:

| UPDATED_PO_DOCUMENT              |
| :------------------------------- |
| {<br>  "existing" : "value"<br>} |

Removing the Special Instructions attribute as per the documentation you linked to:

When merging object members that have the same field:

  • If the patch field value is null then the field is dropped from the source — it is not included in the result.
  • Otherwise, the field is kept in the result, but its value is the result of merging the source field value with the patch field value. That is, the merging operation in this case is recursive — it dives down into fields whose values are themselves objects.

db<>fiddle here

Upvotes: 1

Related Questions