5 The JSON Page

Note:

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 Selector icon to display the left navigation pane. Expand the Development menu and select JSON.

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.

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 JSON documents that belong to the collection are displayed in the right pane.

The two icons in the right corner are:

  • Tour tour icon: Starts the JSON tour, which provides information about the features available.

  • Go to SQL SQL page icon: Navigates to the SQL page.

The icons in the toolbar are:

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

  • Import Document(s) import document icon: Imports one or more existing JSON files from your local computer into the collection.

  • Delete All Documents in the list delete documents icon: Deletes all JSON documents in the collection that match the current QBE search string. If the current string is {}, then all the documents in the collection are deleted.

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

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

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

Each JSON document has the following icons:

  • Edit Document edit document icon: Edits the JSON document. See About Adding or Editing a JSON Document

  • Clone Document clone docment icon: Creates a clone of the document.

  • Copy Document copy document icon: Copies the document to the clipboard.

  • Export Document export document icon: Downloads the document as a .JSON file.

  • Delete Document delete document icon: Deletes the document.

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 collection details icon 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. When you navigate to the JSON page:
    • If there are no collections created, you see the JSON home page. Click Create Collection.

    • If there are existing collections available, then in the left pane, click Create Collection new collection icon.

  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.

    This is the only mandatory field to create a collection. If you do not make any entries in the other fields, the default values apply.

    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.

    • Max Length: Maximum length of the column in bytes (for type VARCHAR2 only).

    • Assignment Method: Method used to assign keys to objects that are inserted into the collection. 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.

    • Max Length: Maximum length of the content column in bytes (for type VARCHAR2 only).

    • 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.

    • LOB Encryption: Specifies the SecureFiles LOB encryption setting.

    • Version Column: Name of the column that stores the document version.

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

    • Media Type Column: Name of the column that stores the media type of the document.

    • Creation Time Column: Name of the column that stores the creation time stamp of the document.

    • Last Modified Column: Name of the column that stores the last-modified time stamp of the document.

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

    Output JSON: Automatically displays the request body JSON payload as you select or enter values for the collection attributes. This text is read only.

  3. Click Create to create the collection.

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

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 New JSON Document icon in the right pane toolbar for a specific collection.

To edit a document, click Edit Document edit document icon in the document card.

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

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. The other icons in the editor are:

  • Format JSON data format json icon: Enables indentation and line feeds.

  • Compact JSON data compact icon: Removes all white spaces.

  • Clear clear icon: Clears the current QBE string.

  • History history icon: Retrieves previous QBE search strings.

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 Run Query run query icon.

Tree Mode

In the Tree mode:

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

    Description of qbe_tree.png follows
    Description of the illustration qbe_tree.png

  2. Click the + symbol before Append.

  3. Enter the field name and value.

    Description of qbe_tree2.png follows
    Description of the illustration qbe_tree2.png

  4. Click Filter filter icon

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.

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.

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 Diagramdiagram icon in the right pane toolbar to display the JSON data guide diagram for a specific collection.

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