3.15 Managing SODA Collections

Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database.

• About SODA Collections
  You can load data to a SODA collection as JSON, TXT, or ZIP files.
• Creating a SODA Collection
  Use SQL Workshop to load documents to a new SODA collection.
• Viewing a SODA Collection
  Access existing SODA collections in Object Browser.
• Editing or Dropping a SODA Collection
  View a SODA collection in Object Browser, then use the Load Data to upload a new version of the data or click the Drop button to remove the SODA collection from the workspace.

3.15.1 About SODA Collections

You can load data to a SODA collection as JSON, TXT, or ZIP files.

SODA collections are an Object Type in Object Browser. You use Object Browser to view or edit existing SODA collections, including drop them from the workspace.

Requirements

• Database 18.3 or higher.
• The workspace user must have the SODA_APP role.

Supported Filetypes

You can upload the following filetypes to a SODA collection:

• JSON files - APEX supports JSON files containing an array with multiple JSON objects. APEX extracts each JSON object from the array and loads it as a single JSON document to the SODA collection. The array selector is auto-detected by default.

• JSON lists - JSON lists are files containing multiple JSON documents, one per line, separated by line feeds.

• ZIP files - ZIP files are automatically extracted and each file can be uploaded to a SODA collection. Depending on data load settings, non-JSON files within the ZIP archive are either skipped or also loaded to the collection. XML files can be converted to JSON before loading to the SODA collection.

3.15.2 Creating a SODA Collection

Use SQL Workshop to load documents to a new SODA collection.

To load documents to a new SODA collection:

1. In Object Browser, click the Create icon ( + ) (top-right).
2. Select SODA Collection.
   The Load Data into SODA Collection dialog displays.

   Tip:

   You can also access the Load Data wizard dialog with the SODA Collection shortcut in the Create Object column on various SQL Workshop pages.
3. Do one of the following
   • Drag and drop the desired JSON, TXT, or ZIP file into the dialog.
   • Click Choose File, browse your local system to the file location, and upload.
   The wizard prepares the next screen based on filetype.
4. Configure the options. For all filetypes, choose an upload option:
   • To create a new collection (default), select New Collection.
   • To upload to an existing collection, select Existing Collection.
5. For SODA Collection Name, enter the name of the collection.

   Tip:

   If Create Relational View is enabled, Relational View Name populates automatically based on SODA Collection Name. You can change the Relational View Name if desired.
6. Click Load Data.
   The data loads. This may take some time.
7. When the upload completes, you can proceed in the following ways:
   • To view the SODA collection in Object Browser, click View Collection.
   • To jump to the Create App wizard (App Builder), click Create Application.
   • To return to Object Browser, click the X icon (top-right) or press Esc to close the dialog.

3.15.3 Viewing a SODA Collection

Access existing SODA collections in Object Browser.

To access existing SODA collections:

1. On the Workspace home page, click SQL Workshop.
2. Click Object Browser.
3. In the Object Selection Pane (left), click the Object Type dropdown menu (Tables is selected by default).
4. Select SODA Collections.
   A list of existing SODA collections displays.
5. Click a collection to view it.
   There are three tabs to view:

   • Collection Contents - View the general contents of the collection. Contains the Load Data and Drop buttons for the collection.
   • Data Guide - View the Relational Views and full JSON Data Guide associated with the collection. Contains the Drop Search Index and Create Relational View buttons for the relational view. (Clicking a view under View Name navigates to Views in Object Browser.)
   • Collection Meta Data - View the SODA REST Endpoint URL and full Collection Meta Data.

3.15.4 Editing or Dropping a SODA Collection

View a SODA collection in Object Browser, then use the Load Data to upload a new version of the data or click the Drop button to remove the SODA collection from the workspace.

To view a SODA collection in Object Browser:

1. In Object Browser, click the Object Type dropdown menu and select SODA Collections.
2. Select the SODA collection to update.

To change a SODA collection:

3. Click the Load Data button.
   The Data Upload wizard opens.
4. Proceed through the wizard, ensuring to select Existing Collection for Load To.

To drop a SODA collection:

5. Click the Drop button.
   The confirmation screen displays.
6. Click the Drop button to confirm.