The JSON Page

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 json_left_pane.png follows

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 JSON documents that belong to the collection are displayed in the lower part of the right pane. The top part of the right pane contains the JSON editor where you can run queries for filtering or sorting the documents.

Description of json_right_pane.png follows

Description of the illustration json_right_pane.png

The two icons in the right corner are:

Tour : Starts the JSON tour, which provides information about the features available.
Go to SQL : Navigates to the SQL page.

The icons in the toolbar are:

New JSON Document : Adds a new document to the collection. See About Adding or Editing a JSON Document
Import Document(s) : Imports one or more existing JSON files from your local computer into the collection.
Delete All Documents in the list : 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 : 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
Run Query : Filters documents using the QBE condition entered in the JSON editor. See About Querying Documents in a Collection

Each JSON document has the following icons:

Edit Document : Edits the JSON document. See About Adding or Editing a JSON Document
Clone Document : Creates a clone of the document.
Copy Document : Copies the document to the clipboard.
Export Document : Downloads the document as a .JSON file.
Delete Document : Deletes the document.

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.

Creating a Collection

This section describes how to create a collection.

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 .
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.
Click Create to create the collection.

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

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. In the JSON editor, you can copy and paste the JSON document or use the Import icon to import the JSON document.

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

The following figure shows an open document in the JSON editor.

Description of the illustration json_editor.png

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, enter the QBE string in the JSON editor. For example, to select documents where the name is Mary, enter {"name":"Mary"} and then click Run Query, as shown in Figure 5-1. The results of the query are displayed in the lower right pane.

The editor offers a comprehensive list of commands available through the Command Palette. To open the Command Palette, press Ctrl+Shift+P. For a list of keyboard shortcut keys, see Keyboard Shortcuts.

An error in the query is signified by a red dot in the left gutter and a squiggle line beneath the specific text. When you hover over it, you see a pop-up displaying possible fixes for resolving the error.

You can set editor preferences using the Preferences option in the top-right user drop-down list on the header. Some of the available options are Theme (Light, Dark and High contrast dark), Font size and family, Tab size, Word wrap, Ruler, Line numbers and so on.

The icons above the editor are:

Format JSON data : Enables indentation and line feeds for the QBE string.
Add Clause: Adds a formatted template of the $orderby or $patch clause to the QBE string.
Clear : Clears the current QBE string.
History : Retrieves previous QBE search strings.

Figure 5-1 QBE String in JSON Editor

Description of "Figure 5-1 QBE String in JSON Editor"

The QBE expression must be a valid JSON object and can contain $query and $orderby or $patch clauses. The QBE expression is treated as a $query clause if there are no clauses defined. Starting with the $query clause, add the $orderby or $patch clause later using the Add Clause list. The content is transformed and a template for the $orderby and $patch clause is provided. You need to set correct values in the templates. See Using the In-Context Autocomplete Feature in the JSON Editor

For example, this is a simple filtering query:

Description of the illustration plain_query.png

After the $orderby clause is added using Add Clause, you see the following entry:

Description of orderby_clause.png follows

Description of the illustration orderby_clause.png

After the $patch clause is added, the query changes to:

Description of the illustration patch_clause.png

Using the In-Context Autocomplete Feature in the JSON Editor

If you press Ctrl+Space, the editor provides you with a context-aware list of options from which you can select and autocomplete at the insertion point.

Description of json_code_completion.png follows
Description of the illustration json_code_completion.png

The following types of information appear in the list:

Filter comparison clauses

A template is available for each clause and it is inserted at the cursor position. For example, when you select the $between clause from the autocomplete list, you see the following entry in the editor:

Description of the illustration filter_clause.png

where age is the property name and 49 and 70 are values. These are parameters that you can edit and they appear highlighted. You can type or use the autocomplete help to edit the property name. Press the Tab key to move to the next parameter.
Property names from JSON documents in the collection

There are two sources for property names from JSON documents:
- If a search index is created with support for data guide, then the JSON data guide from the database dictionary is scanned for property names.
- Property names that are collected from viewed or edited documents are presented. It is possible that the property names are a subset from the whole namespace.
Based on the type of property, the related template is inserted and the cursor is positioned at the expected place for insertion. The following is a list of property types, their templates and the corresponding cursor position for each:
- Object
```
"ShippingInstructions.Address": {
<cursor_here>
}
```
- Array
```
"LineItems": [<cursor_here>]
```
- String
```
"LineItems[*].Part.Description": "<cursor_here>]"
```
- Number
```
"PONumber": 0
```
- Boolean
```
"site_admin": true
```
When the cursor is between double quotes (“”) and autocompletion is activated, then only the property name is inserted without templates or additional double quotes.

Managing JSON Search Indexes for a Collection

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

Create a JSON Search Index

In the left pane, right-click the specific collection, select Search Index and then select Create.
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

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

You are prompted to confirm.
Click OK to drop.

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 json_diagram_json.png follows

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.