Using Document Database with ObjectScript

Get a Database: %GetDatabase()

See Creating a Document Database in ObjectScript for various ways to create a document database.

To get an existing document database in the current namespace, invoke the %GetDatabase()Opens in a new tab method.

You assign a document database a unique name within the current namespace. The name can be qualified "packagename.docdbname" or unqualified. An unqualified database name defaults to the ISC.DM package.

The following example gets a Database if a Database with that name exists in the current namespace; otherwise, it creates a new one. It then uses the %GetDatabaseDefinition()Opens in a new tab method of the db object to display the database definition information, which is stored as a JSON Dynamic Object:

  set EXISTS = $SYSTEM.DocDB.Exists("People")
  if EXISTS set db = $SYSTEM.DocDB.%GetDatabase("People")
  if 'EXISTS set db = $SYSTEM.DocDB.%CreateDatabase("People")

  set defn = db.%GetDatabaseDefinition()
  write defn.%ToJSON()

You can use GetAllDatabasesOpens in a new tab to return a JSON array containing the names of all databases defined in this namespace. For example:

  write $SYSTEM.DocDB.GetAllDatabases().%ToJSON()

Define a Property: %CreateProperty()

In order to retrieve a document by a key:value pair, you must use %CreateProperty()Opens in a new tab to define a property for that key. Defining a property automatically creates an index for that key which InterSystems IRIS maintains when documents are inserted, modified, and deleted. A property must specify a data type for the key. A property can be specified as accepting only unique values (1), or as non-unique (0); the default is non-unique.

The following example assigns two properties to the database. It then displays the database definition information:

  do $SYSTEM.DocDB.DropDatabase("People")
  set db = $SYSTEM.DocDB.CreateDatabase("People")
  do db.%CreateProperty("firstName","%String","$.firstName",0) // creates non-unique property
  do db.%CreateProperty("lastName","%String","$.lastName",1)   // create a unique property and index
  write db.%GetDatabaseDefinition().%ToJSON()

Insert or Replace a Document: %SaveDocument()

You can insert or replace a document in a database using either the documentID or data selection criteria.

The %SaveDocument()Opens in a new tab and %SaveDocumentByKey()Opens in a new tab methods save a document, inserting a new document or replacing an existing document. %SaveDocument() specifies the document by documentId; %SaveDocumentByKey() specifies the document by key name and key value.

If you do not specify a documentId, %SaveDocument() inserts a new document and generates a new documentId. If you specify a documentId, it replaces an existing document with that documentId. If you specify a documentId and that document does not exist, it generates an ERROR #5809 exception.

The document data consists of one or more key:value pairs. If you specify a duplicate value for a key property that is defined as unique, it generates an ERROR #5808 exception.

The %SaveDocument() and %SaveDocumentByKey() methods return a reference to the instance of the database document class. This is always a subclass of %DocDB.DocumentOpens in a new tab. The method return data type is %DocDB.DocumentOpens in a new tab.

The following example inserts three new documents and assigns them documentIds. It then replaces the entire contents of the document identified by documentId 2 with the specified contents:

  do $SYSTEM.DocDB.DropDatabase("People")
  set db = $SYSTEM.DocDB.CreateDatabase("People")
  write db.%Size(),!
  do db.%CreateProperty("firstName","%String","$.firstName",0)
  set val = db.%SaveDocument({"firstName":"Serena","lastName":"Williams"})
  set val = db.%SaveDocument({"firstName":"Bill","lastName":"Faulkner"})
  set val = db.%SaveDocument({"firstName":"Fred","lastName":"Astare"})
  write "Contains ",db.%Size()," documents: ",db.%ToJSON()
  set val = db.%SaveDocument({"firstName":"William","lastName":"Faulkner"},2)
  write !,"Contains ",db.%Size()," documents: ",db.%ToJSON()

The following example chains the %Id() method (inherited from %Library.PersistentOpens in a new tab) to each %SaveDocument(), returning the documentId of each document as it is inserted or replaced:

  do $SYSTEM.DocDB.DropDatabase("People")
  set db = $SYSTEM.DocDB.CreateDatabase("People")
  do db.%CreateProperty("firstName","%String","$.firstName",0)
  write db.%SaveDocument({"firstName":"Serena","lastName":"Williams"}).%Id(),!
  write db.%SaveDocument({"firstName":"Bill","lastName":"Faulkner"}).%Id(),!
  write db.%SaveDocument({"firstName":"Fred","lastName":"Astare"}).%Id(),!
  write "Contains ",db.%Size()," documents: ",db.%ToJSON()
  write db.%SaveDocument({"firstName":"William","lastName":"Faulkner"},2).%Id(),!
  write !,"Contains ",db.%Size()," documents: ",db.%ToJSON()

The final lines of both the previous examples call the Document.%Size() method.

Count Documents in a Database: %Size()

To count the number of documents in a database, invoke the %DocDB.DocumentOpens in a new tab %Size()Opens in a new tab method:

  set db = $SYSTEM.DocDB.GetDatabase("People")
  write db.%Size()

See Insert or Replace a Document for more examples.

Get Document in a Database: %GetDocument()

To retrieve a single document from the database by %DocumentId, invoke the %GetDocument()Opens in a new tab method, as shown in the following example:

  do db.%GetDocument(2).%ToJSON()

This method returns only the %Doc property contents. For example:

{"firstName":"Bill","lastName":"Faulkner"}

The method return type is %Library.DynamicAbstractObject.

If the specified %DocumentId does not exist, %GetDocument() generates an ERROR #5809 exception: “Object to load not found”.

You can retrieve a single document from the database by key value using %GetDocumentByKey()Opens in a new tab.

You can also return a single document from the database by %DocumentId, using the %FindDocuments() method. For example:

  do db.%FindDocuments(["%DocumentId",2,"="]).%ToJSON()

This method returns the complete JSON document, including its wrapper:

{"sqlcode":100,"message":null,"content":[{"%Doc":"{\"firstName\":\"Bill\",\"lastName\":\"Faulkner\"}",
"%DocumentId":2,"%LastModified":"2018-03-06 18:59:02.559"}]}

Delete a Document: %DeleteDocument()

You can delete documents from a database either by documentID or by data selection criteria.

• The %DeleteDocument()Opens in a new tab method deletes a single document identified by documentId:

    set val = db.%DeleteDocument(2)
    write "deleted document = ",val.%ToJSON()

  

  Successful completion returns the JSON value of the deleted document. Failure throws a StatusException error.

• The %DeleteDocumentByKey()Opens in a new tab method deletes a document identified by document contents. Specify a key:value pair.

• The %Clear()Opens in a new tab method deletes all documents in the database and returns the oref (object reference) of the database, as shown in the following example:

    set dboref = db.%Clear()
    write "database oref: ",dboref,!
    write "number of documents:",db.%Size()

  

  This permits you to chain methods, as shown in the following example:

    write db.%Clear().%SaveDocument({"firstName":"Venus","lastName":"Williams"}).%Id(),!

  

Query Documents in a Database: %ExecuteQuery()

You can use the %ExecuteQuery()Opens in a new tab method to return document data from a database as a result set. You specify a standard SQL query SELECT, specifying the database name in the FROM clause. If the database name was created with no package name (schema), specify the default ISC_DM schema.

The following example retrieves the %Doc content data as a result set from all documents in the database:

  set rval=db.%ExecuteQuery("SELECT %Doc FROM ISC_DM.People")
  do rval.%Display()

The following example uses a WHERE clause condition to limit what documents to retrieve by documentId value:

  set rval=db.%ExecuteQuery("SELECT %DocumentId,%Doc FROM ISC_DM.People WHERE %DocumentId > 2")
  do rval.%Display()

The following example retrieve the %DocumentId and the lastName property from all documents that fulfill the WHERE clause condition. Note that to retrieve the values of a user-defined key, you must have defined a document property for that key:

  set rval=db.%ExecuteQuery("SELECT %DocumentId,lastName FROM ISC_DM.People WHERE lastName %STARTSWITH 'S'")
  do rval.%Display()

For further information on handling a query result set, refer to Returning the Full Result Set or Returning Specific Values from the Result Set in Using InterSystems SQL.

Find Documents in a Database: %FindDocuments()

To find one or more documents in a database and return the document(s) as JSON, invoke the %FindDocuments()Opens in a new tab method. This method takes any combination of three optional positional predicates: a restriction array, a projection array, and a limit key:value pair.

The following examples, with no positional predicates, both return all of the data in all of the documents in the database:

  write db.%FindDocuments().%ToJSON()

  write db.%FindDocuments(,,).%ToJSON()

Restriction Predicate Array

The restriction predicate syntax ["property","value","operator"] returns the entire contents of the matching documents. You specify as search criteria the property, value, and operator as an array. If you do not specify the operator, it defaults to "=". You can specify more than one restriction as an array of restriction predicates with implicit AND logic: [["property","value","operator"],["property2","value2","operator2"]]. The restriction predicate is optional.

The following example returns all documents with a documentId greater than 2:

  set result = db.%FindDocuments(["%DocumentId",2,">"])
  write result.%ToJSON()

or by chaining methods:

  write db.%FindDocuments(["%DocumentId",2,">"]).%ToJSON()

If the contents of documents match the search criteria, It returns results such as the following:

{"sqlcode":100,"message":null,"content":[{"%Doc":"{\"firstName\":\"Fred\",\"lastName\":\"Astare\"}","%DocumentId":"3","%LastModified":"2018-03-05 18:15:30.39"},{"%Doc":"{\"firstName\":\"Ginger\",\"lastName\":\"Rogers\"}","%DocumentId":"4","%LastModified":"2018-03-05 18:15:30.39"}]} 

If no documents match the search criteria, it returns:

{"sqlcode":100,"message":null,"content":[]}

To find a document by a key:value pair, you must have defined a document property for that key:

  do $SYSTEM.DocDB.DropDatabase("People")
  set db = $SYSTEM.DocDB.CreateDatabase("People")
  write db.%Size(),!
  do db.%CreateProperty("firstName","%String","$.firstName",0)
  set val = db.%SaveDocument({"firstName":"Fred","lastName":"Rogers"})
  set val = db.%SaveDocument({"firstName":"Serena","lastName":"Williams"})
  set val = db.%SaveDocument({"firstName":"Bill","lastName":"Faulkner"})
  set val = db.%SaveDocument({"firstName":"Barak","lastName":"Obama"})
  set val = db.%SaveDocument({"firstName":"Fred","lastName":"Astare"})
  set val = db.%SaveDocument({"lastName":"Madonna"})
  set result = db.%FindDocuments(["firstName","Fred","="])
  write result.%ToJSON()

You can use a variety of predicate operators, including %STARTSWITH, IN, NULL, and NOT NULL, as shown in the following examples:

  set result = db.%FindDocuments(["firstName","B","%STARTSWITH"])
  write result.%ToJSON()

  set result = db.%FindDocuments(["firstName","Bill,Fred","IN"])
  write result.%ToJSON()

  set result = db.%FindDocuments(["firstName","NULL","NULL"])
  write result.%ToJSON()

  set result = db.%FindDocuments(["firstName","NULL","NOT NULL"])
  write result.%ToJSON()

Projection Predicate Array

To return only some of the values in returned documents you use a projection. The optional projection predicate, ["prop1","prop2",...], is an array listing the keys for which you wish to return the corresponding values. If you specify a user-defined key in the projection array, you must have defined a document property for that key.

The syntax ["property","value","operator"],["prop1","prop2",...] returns the specified properties from the matching documents.

  set result = db.%FindDocuments(["firstName","Bill","="],["%DocumentId","firstName"])
  write result.%ToJSON()

You can specify a projection with or without a restriction predicate. Thus both of the following are valid syntax:

• db.%FindDocuments(["property","value","operator"],[prop1,prop2,...]) restriction and projection.

• db.%FindDocuments(,[prop1,prop2,...]) no restriction, projection.

Limit Predicate

You can specify a limit key:value predicate, {"limit":int}, to return, at most, only the specified number of matching documents.

The syntax ["property","value","operator"],["prop1","prop2",...],{"limit":int} returns the specified properties from the specified limit number of documents.

  set result = db.%FindDocuments(["firstName","Bill","="],["%DocumentID","firstName"],{"limit":5})
  write result.%ToJSON()

This returns data from, at most, 5 documents.

You can specify a limit with or without a restriction predicate or a projection predicate. Thus all the following are valid syntax:

• db.%FindDocuments(["property","value","operator"],,{"limit":int}) restriction, no projection, limit.

• db.%FindDocuments(,[prop1,prop2,...],{"limit":int}) no restriction, projection, limit.

• db.%FindDocuments(,,{"limit":int}) no restriction, no projection, limit.

The following example specifies no restriction, a projection, and a limit:

  write db.%FindDocuments(,["%DocumentId","lastName"],{"limit":3}).%ToJSON()