Use Oracle Database Actions with SQL over SODA Collections

You can use Oracle Database Actions with SQL to work with SODA collections. In this case, you act directly on the backing-store tables or views that underlie SODA collections.

The examples here use the employees SODA collection, emp, created in topic Use Oracle Database Actions with SODA. (That topic creates the collection using Database Actions SODA commands, but the collection could be created, and it can be modified, using any supported SODA language or framework — Java, Node.js, Python, C, PL/SQL, or REST.)

Collection emp has these five employee documents:

{"name" : "Blake", "job" : "Intern", "salary" : 30000}
{"name" : "Smith", "job" : "Programmer", "salary" : 80000}
{"name" : "Miller", "job" : "Programmer", "salary" : 90000}
{"name" : "Clark", "job" : "Manager", "salary" : 100000}
{"name" : "King", "job" : "President", "salary" : 200000,
 "email" : "king@example.com"}

In Database Actions, you can see the complete backing-store database table that underlies this SODA collection in the Navigator tab to the left of the worksheet. In this case, expand EMP there to show the columns of that table.

ID — Document key column.
CREATED_ON — Creation timestamp column.
LAST_MODIFIED — Last-modified timestamp column.
JSON_DOCUMENT — JSON content column (in this case, employee data).

You can use Structured Query Language (SQL) directly on this underlying data.

You enter SQL statements in the Worksheet area of Database Actions, click the green right-arrow, and see results and other information in the tabs below the worksheet. The simple examples here select documents, project JSON fields from them, and perform aggregate operations on selected fields.

Select each of the documents in the collection.
```
SELECT json_serialize(json_document) FROM emp;
```
The documents are listed in tab Script Output, below the worksheet.

Because this query retrieves an entire JSON document you need to convert Oracle's native binary JSON format, OSON, to textual format using standard SQL/JSON function json_serialize. (When you use SQL to retrieve JSON objects or arrays from within JSON documents you need not use json_serialize; that data is automatically serialized to textual format.)
Query the collection, projecting out the value of each of the fields from each document, as a SQL value.
```
SELECT e.json_document.name,
       e.json_document.job,
       e.json_document.salary,
       e.json_document.email
  FROM emp e;
```
The projected field values are listed in Script Output in tabular form. The values for each document form one row of the table.

In the query, we give table EMP the alias e, and we use a simple dot notation <table>.<JSON column>.<field> to target each field.

The simple dot notation is handy for drilling down into JSON data. Just be aware of two particularities with respect to most SQL syntax: (1) A table alias is required when you use dot notation. (2) Although SQL is case-insensitive in general, with the dot notation <field> corresponds to JSON data, so it is interpreted case-sensitively (JSON, like JavaScript, is case-sensitive).

The value for each field except salary is a SQL string (VARCHAR2 data type). The value for field salary is a SQL number (NUMBER data type). The value for field email for employee King is the VARCHAR2 value king@example.com. The value for field email for the other employees is shown as (null), meaning that the field is absent.
Query the collection, projecting field job joining it with the result of an aggregate operation that counts employees that have each job (as a group) across the collection.
```
SELECT e.json_document.job, count(*) FROM emp e
  GROUP BY e.json_document.job;
```
SQL queries over SODA collections can perform arbitrarily complex joins and aggregate operations.

Parent topic: Use Oracle Database Actions with JSON Collections