3.15 Managing SODA Collections

Use Object Browser to load data to a SODA collection as JSON, TXT, or ZIP files.

• About SODA Collections
  Learn about SODA collections.
• Creating a SODA Collection
  Use Object Browser to load documents and create a new SODA collection.
• Viewing a SODA Collection
  View existing SODA collections in Object Browser.
• Editing or Dropping a SODA Collection
  View a SODA collection in Object Browser, then use 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

Learn about SODA collections.

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

SODA uses a SQL schema to store documents .Documents are generally JSON data but they can be any type of data, including video, images, sounds, or other binary content. Documents can be fetched from the database by key lookup or by using query-by-example (QBE) pattern-matching.

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 Object Browser to load documents and create a new SODA collection.

To load documents to a new SODA collection:

1. In Object Browser, click the Create Database Objects menu and select SODA Collection.

   Tip:

   To create new objects from the Object Tree, right-click the object and select the Create option.

   The Create SODA Collection Wizard appears.

2. On Load Data into SODA Collection:
   • Drag and drop the desired JSON, TXT, or ZIP file into the dialog.
   • Click Choose File and browse your local system to the file location to upload it.
   The wizard prepares the next screen based on filetype.
3. JSON Selector Type - Specify how to extract JSON documents from the uploaded file. Options include:
   • Auto-Detect - APEX looks for the first JSON array within the uploaded file and uploads each JSON object within the array as a document to the SODA collection.
   • Custom - Requires that the uploaded file is a valid JSON document. APEX uses the user-provided JSON selector to reference a JSON array within the uploaded file, and uploads each JSON object within the array as a document to the SODA collection.
   • Linefeed (JSON List) - Requires a file containing multiple JSON documents with each document in one line, and separated by line feeds. This type of JSON upload supports individual JSON documents (line length) up to 32,767 bytes.
4. Load To - Decide whether to load into a new or existing SODA collection. Options include:
   • New Collection - Creates a new collection.
   • Existing Collection - Upload to an existing collection.

     If this option is selected, Existing SODA Collection appears. Choose an existing SODA collection to load data into.

     If prompted, choose whether your Update Method appends the data to the collection or replaces the data in the collection.

5. SODA Collection Name - Provide a name for the new SODA collection. Only upper case names are supported.

   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. Skip invalid rows - If enabled, rows containing invalid JSON documents are skipped. If disabled, rows containing invalid JSON documents raise an error.
7. Create Relational View - If enabled and a name for the relational view is provided, APEX uses the Data Guide feature in order to generate a relational view on top of the SODA collection. The relational view can then be used to create an application.
8. Click Load Data.
   When the data has loaded, a confirmation message appears. This may take some time.
9. When the upload completes, you can:
   • View Collection - Click this option to view the SODA collection in Object Browser.
   • Create Application - Click this option to jump to the Create Application Wizard in App Builder.
10. 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

View existing SODA collections in Object Browser.

To view an existing SODA collection:

1. In Object Browser, Object Tree, expand SODA Collections and select a collection.
2. The Object Detail View appears and displays three tabs:
   • Collection Contents - View the general contents of the collection. Available buttons include:
     • Load Data
     • Drop
     • Refresh
   • Data Guide - View the Relational Views and full JSON Data Guide associated with the collection. Available buttons include:
     • Create Search Index
     • Drop Search Index
     • Create Relational View (for the relational view)
     • Refresh

     Tip:

     Clicking a view name navigates you to the view in Object Browser.
   • 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 Load Data to upload a new version of the data or click the Drop button to remove the SODA collection from the workspace.

To edit or drop a SODA collection:

1. In Object Browser, Object Tree, expand SODA Collections and select a collection.
   The Object Detail View appears.
2. To edit the collection and upload more data:
   1. On the Collection Contents tab, click Load Data.
   2. Drag and drop a file into the wizard, or choose a file from your computer.
   3. Proceed through the wizard. For Load To, select Existing Collection.
3. To drop a SODA collection:
   1. On the Collection Contents tab, click Drop.
   2. Confirm your selection and click Drop again.