Creating a table in the NoSQL Database can be done in the console, through the REST API and the SDKs as well as on the command line. You will now create a table on the command line.

Execute this command to derive the name to be assigned to the table:

export tableName=labTable$LAB_ID
echo Table is called $tableName

The next step is the creation of the table with the NoSQL Database service. Execute this next command to have a table created with three columns:

• id - numeric, meaningless primary key
• name - string
• country - string

oci nosql table create --compartment-id $compartmentId --name $tableName --ddl-statement "CREATE TABLE IF NOT EXISTS $tableName (id INTEGER, name STRING, country STRING, PRIMARY KEY(SHARD(id)))" --table-limits="{\"maxReadUnits\": 15, \"maxStorageInGBs\": 1, \"maxWriteUnits\": 15 }"

Check if the table has been created: oci nosql table list --compartment-id $compartmentId

Note: the creation of the table happens asynchronously from the oci nosql table create command. It might be required to execute the list command a few times before the table is present.

This is what your list of table(s) should look like:

You can check the list of NoSQL Database tables in the current compartment in the console: Database | NoSQL Database. https://console.us-ashburn-1.oraclecloud.com/nosql/tables

echo Open the Console at https://console.$REGION.oraclecloud.com/nosql/tables

Resources

OCI Documentation on SQL for Inserting data in NoSQL Database

OCI Documentation on SQL Queries in NoSQL Database

OCI Documentation on REST APIs on NoSQL Database Service

OCI Documentation on Command Line Interface for NoSQL Database Service

Querying data from the table

oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $tableName"

Of course there is no data yet. Let us create a record:

oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName (id, name, country) VALUES (1,\"John\",\"Scotland\")"

If the INSERT keyword is used, the row will be inserted only if it does not exist already. If the UPSERT keyword is used, the row will be inserted if it does not exist already, otherwise the new row will replace the existing one.

And query the table again for all its data: oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $tableName"

And some more records:

oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName  (id, name, country)  VALUES (2,\"Rolando\",\"Mexico\")"
oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName  (id, name, country) VALUES (3,\"Sven\",\"Germany\")"
oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $tableName  (id, name, country) VALUES (4,\"José\",\"Portugal\")"

Check in the console for the NoSQL Table's contents:

echo Open the following URL in the OCI Console to find the current data records in the newly created table: https://console.$REGION.oraclecloud.com/nosql/tables/$tableName/$compartmentId/rows

And query the table again for all its data: oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $tableName"

You will notice that only the first "page" of data is returned. The size of the page depends on the limit on read units that we defined for the page. To get more results, we can increase the number of supported read units. Alternatively, we can retrieve data in multiple pages. Execute the next command to derive the page token representing the page at which to start retrieving results :

query=$(oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $tableName")
page=$(echo $query | jq -r '."opc-next-page"')
echo The page token we need to provide to the query command to fetch the next page of data: $page

And now execute the query that will return this next page of results: oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $tableName" --page $page

Storing JSON documents under a specific key is common approach with NoSQL databases. The OCI NoSQL Database Service is Not Only SQL - it certainly suports SQL and a relational approach (as you have seen in the previous two steps) - as it also support this key/document-store way of storing and retrieving data. We can create a table with one or more columns of type JSON. A record created in such a table contain a JSON document for these columns. The database enforced that only valid JSON documents can be stored. But it does not enforce a specific structure in the JSON document. In other words, the documents do not have to conform to a predefined schema. This is called schemaless (see for example Martin Fowler on Schemaless for some background)

Execute this command to derive the name to be assigned to the table:

export docTableName=labDocumentsTable$LAB_ID
echo Table is called $docTableName

The next step is the creation of the table for schemaless JSON documents with the NoSQL Database service. Execute this next command to have a table created with three columns:

• id - numeric, meaningless primary key
• document - JSON

oci nosql table create --compartment-id $compartmentId --name $docTableName --ddl-statement "CREATE TABLE IF NOT EXISTS $docTableName (id INTEGER, document JSON, PRIMARY KEY(SHARD(id)))" --table-limits="{\"maxReadUnits\": 25, \"maxStorageInGBs\": 1, \"maxWriteUnits\": 25 }"

This command is very similar to the one used in the previous step. Instead of columns of type string there now is a column of type JSON. This table is now a key-document store for JSON documents. It really is that simple.

Check if the table has been created: oci nosql table list --compartment-id $compartmentId

Note: the creation of the table happens asynchronously from the oci nosql table create command. It might be required to execute the list command a few times before the table is present.

Insert a JSON document into the new table:

document='{ "staff":[{"name":"Sven","country":"Germany"},{"name":"José","country":"Portugal"},{"name":"Rolando","country":"Mexico"},{"name":"Arturo","country":"Norway"}]}'
oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $docTableName  (id, document)  VALUES (2,$document)"

And show to how utterly schemaless our database is, let's create an entirely different JSON document (the contents of document special-doc.json), and insert that into the table too:

document=$(cat special-doc.json)
echo $document
oci nosql query execute --compartment-id $compartmentId --statement="INSERT INTO $docTableName  (id, document)  VALUES (5,$document)"

Query the table for all its data: oci nosql query execute --compartment-id $compartmentId --statement="SELECT * FROM $docTableName"

In the next step, let's see how we can query and manipulate the JSON document in the NoSQL database.

The queries that can be executed against the NoSQL Database tables can refer to JSON document properties in both the WHERE and SELECT clauses. Here are some examples of how you can query the contents of the JSON documents.

Query the staff object from all documents that contain such an object: oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.staff FROM $docTableName d WHERE EXISTS d.document.staff"

Only select the country values from the staff members (from the records that even have a staff object in the root of their document) : oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.staff.country FROM $docTableName d WHERE EXISTS d.document.staff"

From the second JSON document, select all countries that alphabetically rank behind Switzerland: oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.values(\$value > \"Switzerland\") FROM $docTableName d WHERE id=5"

Updating JSON Documents

The JSON documents in the NoSQL Database table can be updated in a partial fashion. Here are two examples.

Partially update the second JSON document - change the country name for just the NL entry, from Netherlands to Holland: oci nosql query execute --compartment-id $compartmentId --statement="UPDATE $docTableName d set d.document.NL=\"Holland\" where id=5 returning id, document"

Query the document for key NL to see the change applied: oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.NL FROM $docTableName d WHERE id=5"

In a similar way, the next command will add a staff member in the former JSON document - by adding an element to the array object:

oci nosql query execute --compartment-id $compartmentId --statement="UPDATE $docTableName d ADD d.document.staff {\"name\":\"Dan\",\"country\":\"USA\"} where id=2 returning *"

Query the document for the staff object and verify that Dan was added: oci nosql query execute --compartment-id $compartmentId --statement="SELECT d.document.staff FROM $docTableName d WHERE id=2"

Resources

Documentation NoSQL Database - Querying JSON Data

Partial JSON Document Update in Oracle NoSQL Database - https://blogs.oracle.com/nosql/json-partial-updates-using-the-oracle-nosql-database-query-language

Bonus: Interact with NoSQL Database from Function

In this step, you will work with a very simple Node application that leverages the Node SDK for NoSQL Database. Then you will turn this Node application into a function - and invoke that function to query NoSQL Database on your behalf.

Simple Node Application using the SDK for NoSQL Database

fn init --runtime node nosql-talker

Execute the following snippet - that will do several things including downloading the NoSQL Database SDK for Node (npm install oracle-nosqldb)

cd nosql-talker

npm install oracle-nosqldb --save
npm install

cp ~/.oci/oci_api_key.pem .

cp ~/nosql.js .

Open file nosql.js in the IDE. Check out function nosqlActor and see how the SDK facilitates the interaction for querying and creating records.

This next command creates a snippet of JSON that you need to copy and paste into file nosql.js, starting at line 5:

json="region: \"$REGION\",\n
compartment: \"lab-compartment\",\n 
auth: {\n
    iam : {\n
        tenantId: \"$TENANCY_OCID\",\n
        userId: \"$USER_OCID\",\n
        fingerprint: \"YOUR_FINGERPRINT_FROM FILE ~/.oci/config\",\n
        privateKeyFile: \"oci_api_key.pem\"\n
    }\n
}"
echo "paste JSON fragment in file oci-configuration.js "
echo -e $json

Note: After pasting the snippet into nosql.js, there is one final step before you can run the Node application: Replace the fingerprint mock value with the actual fingerprint value from file ~/.oci/config

And now execute the Node application that interacts with NoSQL Database Cloud Service: node nosql.js

If the execution fails you may have overlooked the step instruction to replace the fingerprint in nosql.js. I kept making that mistake

See the results coming in. Feel free to edit the file nosql.js and manipulate the results.

Create and Deploy Function

Open file func.js. Copy the snippet below and paste it in func.js, completely replacing all current contents:

const fdk=require('@fnproject/fdk')
const nosql=require('./nosql')

fdk.handle(async function(input){
  let name = 'John';
  if (input.name) {
    name = input.name;
  }
  const rows = await nosql.queryRecordByName(name)
  console.log(`\nInside Node nosql-talker function; the query result is in: ${JSON.stringify(rows)}`)
  return {'name':name, 'results': rows}
})

Now deploy the function:

fn -v deploy --app "lab$LAB_ID"

And run it:

echo -n '{ "name":"Rolando"}' | fn invoke lab$LAB_ID nosql-talker and echo -n '{ "name":"Sven"}' | fn invoke lab$LAB_ID nosql-talker

Resources

Tutorial: Connecting an Application to Oracle NoSQL Database Cloud Service

Oracle NoSQL Database Node.js SDK

Drop the tables that were created in this scenario - to clean up after yourself:

oci nosql table delete  --compartment-id $compartmentId --table-name-or-id  $tableName --force
oci nosql table delete  --compartment-id $compartmentId --table-name-or-id  $docTableName --force

Verify if the tables have been dropped:

oci nosql table list --compartment-id $compartmentId

This concludes your first rapid introduction to the NoSQL Database service on Oracle Cloud Infrastructure.