7.1 Collections as Relational Tables

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.

MySQL Shell JavaScript Code

// 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]);

MySQL Shell Python Code

# Get the customers collection as a table
customers = db.get_collection_as_table('customers')
customers.insert('doc').values('{"_id":"001", "name": "Ana", "last_name": "Silva"}').execute()

# Now do a find operation to retrieve the inserted document
result = customers.select(["doc->'$.name'", "doc->'$.last_name'"]).where("doc->'$._id' = '001'").execute()

record = result.fetch_one()

print("Name : %s\n"  % record[0])
print("Last Name : %s\n"  % record[1])

Node.js JavaScript Code

// Get the customers collection as a table
var customers = db.getCollectionAsTable('customers');
customers.insert('doc').values('{"_id":"001", "name": "Ana"}').execute();

C# Code

// Get the customers collection as a table
var customers = db.GetCollectionAsTable("customers");
customers.Insert("doc").Values("{ \"_id\": 1, \"name\": \"Ana\" }").Execute();

Python Code

# Get the customers collection as a table
customers = db.get_collection_as_table("customers")
customers.insert('doc').values({'_id':'001', 'name': 'Ana', 'last_name': 'Silva'}).execute()

# Now do a find operation to retrieve the inserted document
result = customers.select(["doc->'$.name'", "doc->'$.last_name'"]).where("doc->'$._id' = '001'").execute()

record = result.fetch_one()

print('Name : {0}'.format(record[0]))
print('Last Name : {0}'.format(record[1]))

Java Code

// Get the customers collection as a table
Table customers = db.getCollectionAsTable("customers");
customers.insert("doc").values("{\"name\": \"Ana\"}").execute();

C++ Code

// Get the customers collection as a table
Table customers = db.getCollectionAsTable("customers");
customers.insert("doc")
         .values(R"({"_id":"001", "name": "Ana", "last_name": "Silva"})").execute();

// Now do a find operation to retrieve the inserted document
RowResult result = customers.select("doc->'$.name'", "doc->'$.last_name'")
                            .where("doc->'$._id' = '001'").execute();

Row record = result.fetchOne();
cout << "Name : " << record[0] << endl;
cout << "Last Name : " << record[1] << endl;