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 4-2. 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 4-2 QBE String in JSON Editor

Description of "Figure 4-2 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 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 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.