You can use a $sql stage to execute Oracle SQL and PL/SQL code.

Here is an example that uses shell mongosh to execute, as user user100, an aggregation pipeline with a simple $sql stage from a MongoDB client.

insertMany is used to create a collection called emps and inserts three employee documents into it.^Foot 1

user100> db.emps.insertMany([
           {"ename" : "SMITH", "job" : "CLERK",    "sal" : 800},
           {"ename" : "ALLEN", "job" : "SALESMAN", "sal" : 1600},
           {"ename" : "WARD",  "job" : "SALESMAN", "sal" : 1250}
         ]);

Result shown by mongosh:

{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("6595eb06e0fc41db6de93a6d"),
    '1': ObjectId("6595eb06e0fc41db6de93a6e"),
    '2': ObjectId("6595eb06e0fc41db6de93a6f")
  }
}

A SQL SELECT query is used to compute the average of the employee salaries for each job. The average is computed using SQL function AVG.

user100> db.aggregate([ {$sql :
           `SELECT e.data.job, AVG(e.data.sal) average
              FROM emps e
              GROUP BY e.data.job`
         } ]);

The query returns two JSON objects with fields JOB and AVERAGE.

[ 
  { JOB: 'CLERK', AVERAGE: 800 },
  { JOB: 'SALESMAN', AVERAGE: 1425 }
]

A $sql stage has the following syntax. The fields other than $sql are described in Table 3-25.

{$sql    : {statement : <SQL statement>,
 binds   : <variables>,
 dialect : <dialect>,
 format  : <format>}}

The abbreviated syntax {$sql : <SQL statement>} is equivalent to this syntax {$sql : {statement : <SQL statement>}}.

<SQL statement> is the Oracle SQL statement to execute.

• If $sql is the only stage in the pipeline and the pipeline has no starting collection, then <SQL statement> can be any Oracle SQL or PL/SQL code, including SQL data definition language (DDL) and data manipulation language (DML) code.

  For example, this code uses a SQL UPDATE statement to increase the salaries of all employees,by 10 percent:

  db.aggregate([ {$sql :
                  {statement :
                   "UPDATE employees SET salary = salary * 0.1"}} ]);

• Otherwise, either the pipeline is executed on a collection or it has multiple stages. In this case:

  • <SQL statement> must be a SELECT statement that projects a single JSON-type column.

  • The SELECT statement can refer to the output from the input collection or the previous stage using the database view (row source) named INPUT, which has a single JSON-type column DATA containing the input documents.

    See also Query JSON Data in Oracle AI Database JSON Developer’s Guide.

  For example, the following code acts on starting collection orders. It has three stages:

  • Stage $match filters collection orders, choosing only the documents with a status field that has value closed.

  • Stage $sql takes as input the filtered documents output from stage $match. It obtains them from column data of view input (alias v). While selecting the documents, it uses Oracle SQL Function JSON_MERGEPATCH to add a system timestamp to them as the value of new field updated. The resulting timestamped documents are returned as the output from stage $sql.

  • Stage $out creates a new collection, closed_orders, using the output of stage $sql, that is, the documents returned as the result of the SQL SELECT statement.

  db.orders.aggregate([ {$match : {status : "closed"}},
                        {$sql :
                         `SELECT json_mergepatch(
                                   v.data,
                                   JSON {'updated' : SYSTIMESTAMP})
                            FROM input v`},
                        {$out : "closed_orders"} ]);
  

  This query returns a document from the new collection, closed_orders:

  db.closed_orders.findOne()
  

  {
    _id: ObjectId('65e8b973ca4d0a3a255794c8'),
    order_id: 12382,
    product: 'Autonomous Database',
    status: 'closed',
    updated: ISODate('2024-03-06T18:44:23.275Z')
  }

These SQL statements are not supported by stage $sql:

• Statements that use OUT parameters or invoke stored procedures directly (see Subprogram Parameter Modes and SQL Statements for Stored PL/SQL Units)

• Data Manipulation Language (DML) statements that use a returning clause and return variables (see DML Returning)

All stages return zero or more JSON objects as their result. The result for a $sql stage depends on whether or not the SQL statement executed is a SELECT statement.

• For a SELECT statement, each row in the query result set is mapped to a JSON object in the $sql stage result. See $sql Stage Result for a SELECT Statement.

• For a non-SELECT statement, the $sql stage result is a JSON object with the single field result, whose value indicates the number of table rows that the statement changed. See $sql Stage Result for a Non-SELECT Statement.

db.aggregate([ {$sql :
                 {statement :
                   `INSERT INTO emp(empno, ename)
                      VALUES (:empno, :ename)`,
                  binds     :
                   [ {"empno" : "E123", "ename" : "Abdul J."},
                     {"empno" : "E456", "ename" : "Elena H."},
                     {"empno" : "E789", "ename" : "Francis K."} ]}} ]);

db.aggregate([ {$sql :
                 {statement :
                   `INSERT INTO emp(empno, ename) 
                      VALUES (:empno, :ename)`,
                  binds : [ [ {name  : empno,
                               value : "E123"},
                              {name : ename,
                               value : "Abdul J."} ],
                            [ {name  : empno,
                               value : "E456"},
                              {name  : ename,
                               value : "Elena H."} ],
                            [ {name  : empno,
                               value : "E789"},
                              {name  : ename,
                               value : "Francis K."} ] ]}} ]);

db.aggregate([ {$sql :
                 {statement :
                   `INSERT INTO emp(empno, ename) 
                      VALUES (:empno, :ename)`,
                  binds : [ [ "E123", "Abdul J." ],
                            [ "E456", "Elena H." ],
                            [ "E789", "Francis K." ] ]}} ]);

SELECT JSON{*} data FROM deptFoot 2

[ {DEPTNO : 10, DNAME : 'ACCOUNTING', LOC : 'NEW YORK'},
  {DEPTNO : 20, DNAME : 'RESEARCH',   LOC : 'DALLAS'},
  {DEPTNO : 30, DNAME : 'SALES',      LOC : 'CHICAGO'},
  {DEPTNO : 40, DNAME : 'OPERATIONS', LOC : 'BOSTON'} ]

SELECT JSON{'_id' : deptno, 'name', dname} data FROM deptFoot 3

[ {_id : 10, name : 'ACCOUNTING'},
  {_id : 20, name : 'RESEARCH'},
  {_id : 30, name : 'SALES'},
  {_id : 40, name : 'OPERATIONS'} ]

SELECT deptno, dname, loc FROM dept

[ {DEPTNO : 10, DNAME : 'ACCOUNTING', LOC : 'NEW YORK'},
  {DEPTNO : 20, DNAME : 'RESEARCH',   LOC : 'DALLAS'},
  {DEPTNO : 30, DNAME : 'SALES',      LOC : 'CHICAGO'},
  {DEPTNO : 40, DNAME : 'OPERATIONS', LOC : 'BOSTON' } ]

SELECT deptno "id", loc "location", SYSTIMESTAMP "ts" FROM dept

[ {id       : 10,
   location : 'NEW YORK',
   ts       : ISODate("2023-12-01T20:44:17.118Z")},
  {id       : 20,
   location : 'DALLAS',
   ts       : ISODate("2023-12-01T20:44:17.118Z")},
  {id       : 30,
   location : 'CHICAGO',
   ts       : ISODate("2023-12-01T20:44:17.118Z")},
  {id       : 40,
   location : 'BOSTON',
   ts       : ISODate("2023-12-01T20:44:17.118Z")} ]

db.aggregate([{$sql:`CREATE TABLE employee (name VARCHAR2(4000), job
      VARCHAR2(4000))`}])

[ {result : 0} ]

db.aggregate([ {$sql : "INSERT INTO employee VALUES ('Bob', 'Programmer')"} ]);

[ {result : 1} ]

db.aggregate([ {$sql :
                 {statement : "INSERT INTO employee VALUES (:name, :job)",
                  binds     : [ {"name" : "John",    "job" : "Programmer"},
                                {"name" : "Jane",    "job" : "Manager"},
                                {"name" : "Francis", "job" : "CEO"} ]}}]);

[ {result : [ 1, 1, 1 ]} ]

db.aggregate([ {$sql : `DELETE FROM employee e WHERE e.job = 'Programmer'`} ])

[ {result : 2} ]

You can use an $external stage to access data from external files.

Using JSON data stored in an external file you can, for example:

• Use stage $match to filter documents in the file. See Example 3-10.

• Use stage $group to group documents in the file.

• Use stage $out to store the output of stage $external in a new JSON collection. See Example 3-10.

An $external stage has the following syntax. The fields are described in Table 3-29.

{$external : {location   : <URL or file name>,
              directory  : <database directory name>,
              credential : <credential name
              path       : <SQL/JSON path expression>}}

The abbreviated syntax {$external : <pre-authenticated URI>} is equivalent to this syntax {$external : {location : <pre-authenticated URI>}}.

^Footnote 4 Accessing external files using the Internet requires PL/SQL package DBMS_CLOUD. It is pre-installed for Oracle Autonomous AI Database, but you must install and configure it for a non-autonomous database.

For any URI for which you don't need authentication, so you don't need to specify a credential, if you also don't need to specify a path then you can just use the location URL as the value of field $external. See Example 3-10. No authentication is needed for public repositories, files in public buckets, or pre-authenticated URIs.

If fields location and directory are both present, then if their values are both are valid then the directory field is ignored; otherwise an error is raised.

db.aggregate([ {$sql :
                 `BEGIN
                    DBMS_CLOUD.create_credential(
                      credential_name => 'MYCRED',
                      username        => 'myuser@example.com',
                      password        => 'XXXXXXXXX');
                  END;`} ])

[ {"_id" : {"$oid" : "663bce1c219cb9c411e8a719"},
   "a"   : {"b"    : [ {"z" : {"b" : 1, "c" : 99}},
                       {"z" : {"b" : 2}},
                       {"z" : {"a" : 5}},
                       {"z" : {"b" : 1, "a" : 5}} ]}},
  {"_id" : {"$oid" : "663bce1c219cb9c411e8a71a"},
   "a"   : {"b"    : [ {"z" : {"b" : 1, "c" : 99}},
                       {"z" : {"b" : 2}},
                       {"z" : {"a" : 5}} ]}} ]

db.aggregate([ {$external :
                 {location   :
                   "https://private-repo.example.com/.../array.json",
                  credential : "MYCRED",
                  path       : "$[*]"}} ])

[ {_id : ObjectId("663bce1c219cb9c411e8a719"),
   a   : {b : [ {z : {b : 1, c : 99}},
                {z : {b : 2}},
                {z : {a : 5}},
                {z : {b : 1, a : 5}} ]}}
  {_id : ObjectId("663bce1c219cb9c411e8a71a"),
   a   : {b : [ {z : {b : 1, c : 99}}, {z : {b : 2}}, {z : {a : 5}} ]}} ]

db.aggregate([ {$external :
                 {location  : "array.json",
                  directory : "DEMO",
                  path      : `$.a.b[*].z`}}])

[ {b : 1, c : 99},
  {b : 2},
  {a : 5},
  {b : 1, a : 5},
  {b : 1, c : 99},
  {b : 2},
  {a : 5} ]

db.aggregate([ {$external :
                  "https://public-repo.example.com/.../orders.json"},
               {$match : {status : "closed"},
               {$out : "closed-orders"} ])

Restrictions on the use of stage $lookup are described.

You can use a $lookup stage to join documents from another collection on given fields. The following restrictions apply to the fields of stage $lookup:

• Field let is not supported. An error is raised if you use it.

• If the value of field localField is a field that's missing from any input documents then an error is raised. (The missing field is not treated as if it were present with the value of null.)

• If the value of field localField is an array, or if it is a field that occurs more than once in an input document (thus producing multiple values), then an error is raised.

• If the value of field foreignField is a field that's missing from the input documents then no documents match (no error is raised). This includes the case where the document field specified by field localField is missing or has a null value.

• Field pipeline can contain only stages $lookup, $project, and $sort; otherwise, an error is raised. Fields $lookup and $project must not alter any fields referenced by field foreignField; otherwise, an error is raised.