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.

# 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])