Inserting new JSON document into Oracle Autonomous JSON Database

Question

With Database Actions (SQL Developer Web), it's quite easy to click on the 'New JSON Document' button to add a new JSON document to the collection.

The collection of course is actually a table in Oracle, and the table itself has a number of columns:

I have created modules in ORDS with PL/SQL handlers. While I am able to update JSON documents here by using

UPDATE "Collection" SET json_document = '{"key": "value"}' WHERE JSON_VALUE(json_document, '$.id') = :id'

I am not able to add a new document easily with

INSERT INTO "Collection" (json_document) VALUES ('{"key": "value"}')

because the id is set as a PK column and must be specified. How might I use PL/SQL to add a new document with auto generated fields elsewhere? Or should I use SODA for PL/SQL to achieve this only?

Thanks!

David Min · Accepted Answer · 2021-12-23 09:05:48Z

2

You use the dbms_soda package to access the collection. Then use the methods on soda_colletion_t to manipulate it.

For example:

soda create students;
declare
  collection  soda_collection_t;
  document    soda_document_t;
  status      number;
begin
  -- open the collection
  collection := dbms_soda.open_collection('students');
  document   := soda_document_t(
    b_content => utl_raw.cast_to_raw ( 
      '{"id":1,"name":"John Blaha","class":"1980","courses":["c1","c2"]}'
    )
  );

  -- insert a document
  status := collection.insert_one(document);
end;
/

select * from students;

ID                               CREATED_ON               LAST_MODIFIED            VERSION                          JSON_DOCUMENT   
-------------------------------- ------------------------ ------------------------ -------------------------------- --------------- 
A92F68753B384F87BF12557AC38098CB 2021-12-22T14:15:12.831Z 2021-12-22T14:15:12.831Z FE8C80FED46A4F18BFA070EF46073F43 [object Object]

For full documentation and examples on how to use SODA for PL/SQL, see here.

edited Dec 23, 2021 at 9:05

David Min

1,52514 silver badges35 bronze badges

answered Dec 22, 2021 at 14:15

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

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

2 Comments

David Min Over a year ago

OK, so looks like I'll have to use SODA instead of some plain SQL?

Chris Saxon Over a year ago

To change documents in the collection, yes. You can still use plain SQL for querying

Collectives™ on Stack Overflow

Inserting new JSON document into Oracle Autonomous JSON Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related