Hi everyone,

 

It's me again😁. As usual I would like to share something I study recently, and today, I would like to share about the document DB😁.

1st of all, let's go to the documentation https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GDOCDB_intro
Well, I must say, this one is much better to follow than the others, which I read before. 😭 Finally😭😭

 

Ok, let's start walking through it together

Here we go😀, we start a terminal from VSCode to connect to our IRIS

We are going to created Document DB demo.docpatient
 

 

1.1 Check the Document DB exist or not

In the terminal, run the following script to check if the Document DB demo.docpatient  exist or not

w $SYSTEM.DocDB.Exists("demo.docpatient")

If the Document DB exist, 1 will return,

If the Document DB does not exist, 0, will return.

 

1.2 Create a Document Database

If the return is 0, create a Document DB demo.docpatient by the following script

set docdb = ##class(%DocDB.Database).%CreateDatabase("demo.docpatient")

After running this script, the object docdb is pointing to the created Document DB demo.docdpatient.

You my check the property of the object docdb by the following script.

zw docdb

 

1.3 Check the Document DB class from VSCode

A class file demo.docpatien.cls is generated

Below is the generated class file look like

 

1.4 Check the Document DB by SQL

By using the SQL tool form the management portal

Run the following SQL in the Execute Query tab

SELECT * FROM demo.docpatient

 

You will found there table demo.docpatient with the following fields

ID, %Doc, %DocumentId, %LastModified

%Doc will be the field where the document will store

%DocumentId is the unique ID field of each document

%LastModified is the timestamp for last modification

 

---

2 Storing a Document into the Document DB

In this example, we assume we get a few JSON strings looks like below and would like to store into our document DB.

{"pid":"1","BirthDate":"2003-01-01","Gender":"F","Name":"kate"}
{"pid":"2","BirthDate":"2001-02-07","Gender":"M","Name":"liam"}
{"pid":"3","BirthDate":"2013-03-14","Gender":"M","Name":"mario"}

 

2.1 Define the Document Database Object for DB Access

Before writing the document into the Document db, we need to define an object to point to the Document db which we would like to access

Below are the common steps for defining a Document Database Object

2.1.1 Check the Document DB exist or not

In the terminal, run the following script to check if the Document DB demo.docpatient  exist or not

w $SYSTEM.DocDB.Exists("demo.docpatient")

 

2.1.2 Define the Document DB object by %GetDatabase()

If the database exist, you may define the Document DB object docdb by the following script

set docdb = ##class(%DocDB.Database).%GetDatabase("demo.docpatient")

 

2.1.3 Create Property for the Document DB

Now we plan to insert the following data into the document  

{"pid":"1","BirthDate":"2003-01-01","Gender":"F","Name":"kate"}

Before we insert the data, let’s go through the data 1^st. By looking through the data, we might interest to create the index for certain fields. In this example, we would like to set the key "Name" as the index field.

 

Create Property for the Document DB

Run the following script for creating  a NON UNIQUE property Name for the Document DB

w docdb.%CreateProperty("Name","%String","$.Name",0)

 

Check the Document DB class from VSCode

You will find out that 2 extra lines are added to the class

These limes indicate an extra field is added to table (in SQL point of view) and index is added to this filed.

 

Check the Document DB by SQL

Now, switch to the management portal. Run the following SQL in the Execute Query textbox

SELECT * FROM demo.docpatient

You can see and extra field Name is added

 

2.1.4 Insert the Document into the Document DB

 

Insert the Document into the Document DB

To save the document into the Document DB demo.docpatient  we can run the following script

set val = docdb.%SaveDocument({"pid":"1","BirthDate":"2003-01-01","Gender":"F","Name":"kate"})

 

Check the result from VS code

Run the following script to check the Document DB size (number of documents contains in the DB)

write "Contains ", docdb.%Size(), !

 

Run the following script to check the list the documents data

write "Documents:  ", docdb.%ToJSON(), !

 

Check the result from SQL

Run the following SQL in the Execute Query tab

SELECT * FROM demo.docpatient

 

Insert the Document into the Document DB (the rest of them)

Insert the following 2 JSON string and check the result

set val = docdb.%SaveDocument({"pid":"2","BirthDate":"2001-02-07","Gender":"M","Name":"liam"})

set val = docdb.%SaveDocument({"pid":"3","BirthDate":"2013-03-14","Gender":"M","Name":"mario"})

---

 

3 Querying the documents in the Document DB

 

3.1 Getting the document by %DocumentId

 

After open the document DB, we are going to retrieve a document with the %DocumentId=2

You may try the following script

w docdb.%FindDocuments(["%DocumentId",2,"="]).%ToJSON()

 

3.2 Getting the document by %DocumentId with restrict request

Let’ try to get all the documents with %DocumentId < 3

w docdb.%FindDocuments(["%DocumentId",3,"<"]).%ToJSON()

This actually is doing a SQL search for the fields (property), which exist in the Documents DB table, in this example, we created a property Name in the pervious steps. Now we can make use of this property as a finding criteria.

Let’s try to get the documents with Name start with k

w docdb.%FindDocuments(["Name","k","%STARTSWITH"]).%ToJSON()

How about we would like to check out the %DocumentId by the Name start with m. The result should contain Name and %DocumentId and limit the result to 5

w docdb.%FindDocuments(["Name","m","%STARTSWITH"],["Name","%DocumentId"],{"limit":5}).%ToJSON()

 

3.3 Querying the document by SQL

 

Alternatively, we can query the document data by SQL

For example, we are going to retrieve a document with the %DocumentId=2

set sqlresult=docdb.%ExecuteQuery("SELECT %Doc FROM demo.docpatient where %DocumentId=2")

And then display the result

do sqlresult.%Display()

How about we would like to check out the %DocumentId by the Name start with k. The result should contain Name and %DocumentId and limit the result to 5

set sqlresult=docdb.%ExecuteQuery("SELECT Top 5 Name, %DocumentId  FROM demo.docpatient where Name %STARTSWITH 'k' ")

 

And then display the result

do sqlresult.%Display()

An alternative printing way

set sqlresult=docdb.%ExecuteQuery("SELECT Top 5 Name, %DocumentId  FROM demo.docpatient where Name %STARTSWITH 'l' ")

while sqlresult.%Next()  { do sqlresult.%Print() }

Or

set sqlresult=docdb.%ExecuteQuery("SELECT Top 5 Name, %DocumentId  FROM demo.docpatient where Name %STARTSWITH 'k' or Name %STARTSWITH 'm'  ")

while sqlresult.%Next()  { w "Name: "_sqlresult.%Get("Name")_", %DocumentId: "_ sqlresult.%Get("%DocumentId"),!}

 

---

4 Updating the documents in the Document DB

Replace the existing Document by document id

Now we would like to replace the 1^st document with the following JSON string

 

Getting the document by %DocumentId

After open the document DB, we are going to retrieve a document with the %DocumentId=1, for checking the current data

You may try the following script

w docdb.%FindDocuments(["%DocumentId",1,"="]).%ToJSON()

 

Updating the document by %DocumentId

Now we would like to replace the 1^st document with the following JSON string

{"pid":"1","BirthDate":"2003-11-01","Gender":"F","Name":"kateL"}

Try to run the following script

set val = docdb.%SaveDocument({"pid":"1","BirthDate":"2003-11-01","Gender":"F","Name":"kateL"},1)

View the result by making the query again

w docdb.%FindDocuments(["%DocumentId",1,"="]).%ToJSON()

 

---

I think that's all about my study😁 Enjoy!!😀