Applications that seek to store standard SQL columns with
Documents can cast a collection to a table. In this case a
collection can be fetched as a Table object with the
Schema.getCollectionAsTable()
function. From
that moment on it is treated as a regular table. Document values
can be accessed in SQL CRUD operations using the following syntax:
doc->'$.field'
doc->'$.field'
is used to access the
document top level fields. More complex paths can be specified as
well.
doc->'$.some.field.like[3].this'
Once a collection has been fetched as a table with the
Schema.getCollectionAsTable()
function, all SQL
CRUD operations can be used. Using the syntax for document access,
you can select data from the Documents of the Collection and the
extra SQL columns.
The following example shows how to insert a JSON document string
into the doc
field.
// Get the customers collection as a table
var customers = db.getCollectionAsTable('customers');
customers.insert('doc').values('{"_id":"001", "name": "Ana", "last_name": "Silva"}').execute();
// Now do a find operation to retrieve the inserted document
var result = customers.select(["doc->'$.name'", "doc->'$.last_name'"]).where("doc->'$._id' = '001'").execute();
var record = result.fetchOne();
print ("Name : " + record[0]);
print ("Last Name : " + record[1]);