5 The JSON Page

Available for Oracle Database release 19c and later releases and only if you signed in as a database user with the SODA_APP role.

Use the JSON page in SQL Developer Web to view and manage JSON collections, search for collection items using Query-by-Example (QBE), create JSON search indexes and display data guide diagrams for collections.

To navigate to the JSON page, do either of the following:

• In the Database Actions page, click JSON.

• Click Selector to display the left navigation pane. Expand the Development menu and select JSON.

Topics

• About the JSON User Interface

• Managing JSON Collections

• Managing JSON Search Indexes for a Collection

• Viewing the JSON Data Guide Diagram for a Collection

• About Querying Documents in a Collection

See Also:

SODA Overview in Oracle Database Introduction to Simple Oracle Document Access (SODA)

5.1 About the JSON User Interface

The JSON user interface consists of the left pane for listing and searching saved collections and the right pane for viewing and managing documents in a collection.

Listing and Searching JSON Collections

The following figure shows the main items in the left pane of the JSON page.

Description of the illustration json_left_pane.png

Select the appropriate option in the drop-down list to display saved JSON collections or recently accessed JSON collections.

The search functionality is not case-sensitive, retrieves all matching entries, and does not require the use of wildcard characters.

Click the Create Collection icon to create a new collection. See Creating a Collection

Right-click a collection name to open the context menu. The available options are:

• Search index: Create a JSON search index for the collection. You also have the option to drop the created search index. See Managing JSON Search Indexes for a Collection

• Drop: Removes the collection from the database completely.

Viewing the Contents of a Collection

When a specific collection is selected in the left pane, the contents of the collection are displayed in the right pane.

Description of the illustration json_right_pane.png

The icons in the toolbar are:

• New JSON Document : Adds a new document to the collection. See About Adding or Editing a JSON Document

• Collection Details : Displays the properties of a collection. For a description of the fields, see Creating a Collection.

• Diagram : Displays the the JSON data guide as a diagram in a hierarchical format. See Viewing the JSON Data Guide Diagram for a Collection

• There are two views available for documents. In both views, each document entry displays a unique document identifier and the created timestamp. Click the delete icon to delete a document.

  • Card View: Displays each document in a card format. This is the default view.

  • List View: Displays the documents in a list.

• Filter : Filters documents using the QBE condition entered in the Query or Order By field. See About Querying Documents in a Collection

5.2 Managing JSON Collections

You can add, view or drop collections, or browse, add, edit and delete JSON documents in a collection.

• Create a Collection: See Creating a Collection

• Add or Edit Documents in a Collection: See About Adding or Editing a JSON Document

• View Documents in a Collection: Select the collection name in the left pane. The documents in the collection are displayed in the right pane.

• View Collection Details: Select the specific collection in the left pane, and then click Collection Details in the right pane toolbar to view collection properties.

• Drop a Collection: Right-click a collection name in the left pane to open the context menu. Select Drop to remove the collection from the database completely.

5.2.1 Creating a Collection

This section describes how to create a collection.

1. In the left pane, click Create Collection .
2. Enter the collection name and click Create. A brief description of each field is mentioned below:

   Collection Name: Enter a name for the collection. Note that the collection name is case sensitive. By default, this is the only field where you need to make an entry to create a collection. All others fields have default pre-filled values.

   General

   • Search Index: Enter a name to create the JSON search index for the collection.

   • Schema: Name of the Oracle Database schema that owns the table or view to which the collection is mapped.

   • Table: Name of the table or view to which the collection is mapped.

   Key Column

   • Column Name: Name of the column that stores the document key. The default value is ID.

   • Data Type: SQL data type of the column that stores the document key. The default value is VARCHAR2.

   • Max Length: Maximum length of the column in bytes (for type VARCHAR2 only). The default value is 255.

   • Assignment Method: Method used to assign keys to objects that are inserted into the collection. The default value is UUID. If you specify the method as SEQUENCE, then you must also specify the name of that sequence.

   Content Column

   • Column Name: Name of the column that stores the document content. The default value is JSON_DOCUMENT.

   • Data Type: SQL data type of the column that stores the document content. The default value is BLOB.

   • Max Length: Maximum length of the content column in bytes (for type VARCHAR2 only). The default value is 4000.

   • JSON Validation: Syntax to which JSON content must conform: Standard, Strict or Lax.

   • LOB Compression: Specifies the SecureFiles LOB compression setting.

   • LOB Cache: Specifies the SecureFiles LOB cache setting. The default is enabled.

   • LOB Encryption: Specifies the SecureFiles LOB encryption setting.

   • Version Column: Name of the column that stores the document version. The default value is VERSION.

   • Generation Method: Method used to compute version values for objects when they are inserted into a collection or replaced. The default value is SHA256.

   • Media Type Column: Not supported in the 20.3 release.

   • Creation Time Column: Name of the column that stores the creation time stamp of the document. The default value is CREATED_ON.

   • Last Modified Column: Name of the column that stores the last-modified time stamp of the document. The default value is LAST_MODIFIED.

   • Index: Name of the index on the last-modified column. The index is created if a name is specified.

3. Click Create to create the collection.

   Click Cancel or press the Esc key to cancel the collection.

See Also:

SODA Collection Metadata Components in Oracle Database Introduction to Simple Oracle Document Access (SODA)

5.2.2 About Adding or Editing a JSON Document

You can add and edit JSON documents using the JSON editor.

To add a document, click New JSON Document in the right pane toolbar for a specific collection.

To edit a document, click the specific document in the collection.

The following figure shows a document edited in the Tree mode in the JSON editor.

Description of the illustration json_editor.png

The JSON editor enables you to view, edit, format and validate JSON documents. The various modes in the editor are:

• Form: Modify values but not the data structure.

• View: Cannot modify data structure or values.

• Text: Use to copy or type content for a new JSON document.

• Tree: Add, change, remove, move and duplicate fields and values.

For form, view and tree modes, you can use the Seach field in the top right corner to search for and highlight matching text in the document.

5.3 About Querying Documents in a Collection

You can search for one or more documents in a collection by using a filter specification or Query-by-Example (QBE).

A QBE is a pattern expressed in JSON. You use it to select, from a collection, the JSON documents whose content matches it, meaning that the condition expressed by the pattern evaluates to true for the content of only those documents. For more information about QBEs and how to use them, see Overview of SODA QBEs in Oracle Database Introduction to Simple Oracle Document Access (SODA).

For a specific collection, you can enter the QBE filter in the Query or Order By fields in the right pane. The Query field is used to enter the filter condition and the Order By field is used to sort query results in ascending or descending order. You can use these fields separately or together to filter the documents.

Both fields are JSON editors, where you can select the mode and enter the condition that you want to use for filtering the documents.

There are two modes available in the editor: Text and Tree.

Text Mode

Enter the filter condition in the Query field. For example, to select documents where the name is Smith, enter {"name":"Smith"} and then click Filter .

Description of the illustration qbe_text.png

Tree Mode

In the Tree mode:

1. Click the box to the left of (empty object).

   
   Description of the illustration qbe_tree.png

2. Click the + symbol before Append.

3. Enter the field name and value.

   
   Description of the illustration qbe_tree2.png

4. Click Filter

Sort Results in Text Mode Using Order By

To sort the names of employees in descending order, use the expression as shown in the following figure. In the Order By field, a value of 1 specifies ascending order while a minus symbol (-1) specifies descending order.

Description of the illustration qbe_orderby.png

5.4 Managing JSON Search Indexes for a Collection

You can create or drop a JSON search index for a collection.

Create a JSON Search Index

1. In the left pane, right-click the specific collection, select Search Index and then select Create.

2. Enter a name for the search index and click OK.

   The search index name is displayed when viewing collection details.

   
   Description of the illustration json_search_index.png

Drop a Search Index

1. In the left pane, right-click the specific collection, select Search Index and then select Drop.

   You are prompted to confirm.

2. Click OK to drop.

5.5 Viewing the JSON Data Guide Diagram for a Collection

The diagram view displays the JSON data guide for a collection as a hierarchical structure using a format similar to entity-relationship diagrams.

The JSON data guide represents the JSON schema for documents that have a column with JSON content.

In the diagram, arrays are presented as one-to-many relationships, contained objects as one-to-one relationships, and "oneOf" constructs as a box that surrounds possible choices.

Click Diagram in the right pane toolbar to display the JSON data guide diagram for a specific collection.

Description of the illustration json_diagram_json.png

The icons in the toolbar are Print Diagram, Save to SVG format. Zoom In, Zoom Out, Fit Screen and Actual Size.

See Also:

JSON Data Guide in Oracle Database JSON Developer’s Guide