Skip to main content

Using Document Database with ObjectScript

The InterSystems IRIS® data platform Document Database (%DocDB) supplies methods that enable you to work with Document Databases in ObjectScript. For further details on invoking JSON methods from ObjectScript, refer to the Using JSON manual.

Components of ObjectScript %DocDB

The ObjecScript package name for the Document Database is %DocDB. It contains the following classes:

For full details on all methods and properties, see the entries in the Class Library.

Using the $SYSTEM.DocDB Interface

In ObjectScript, some important %DocDB methods can optionally be accessed via the special $SYSTEM interface (implemented in %SYSTEM.DocDBOpens in a new tab). For example, you can get a list of all entry points by calling:

   do $SYSTEM.DocDB.Help()

The following methods are available:

Creating a Database with ObjectScript

A Database is an instance of an ObjectScript persistent class that extends abstract class %DocDB.DocumentOpens in a new tab. A separate Database must be created for each namespace used by Document Database. Only one Database is required per namespace. Commonly, it is assigned the same name as the namespace name.

The following example shows how to create a Database through class definition:

Class MyDBs.People Extends %DocDB.Document [ DdlAllowed ]
{
}

The following example shows how to create a Database using the CreateDatabase()Opens in a new tab method, specifying a package name:

  set personDB = $SYSTEM.DocDB.CreateDatabase("MyDBs.People")

This example creates the Database using the default package name:

  set personDB = $SYSTEM.DocDB.CreateDatabase("People")

Document Storage

A Document is stored in the %DocOpens in a new tab property of an instance of the %DocDB.DocumentOpens in a new tab class you create. This is shown in the following example, which stores a JSON array in the %Doc property:

  do $SYSTEM.DocDB.DropDatabase("MyDBs.People")
  set personDB = $SYSTEM.DocDB.CreateDatabase("MyDBs.People")
  set myOref = ##class(MyDBs.People).%New()
  set myOref.%Doc = ["Anne","Bradford","Charles","Deborah"]
  set docOref = myOref.%Doc
  write "%Doc property oref: ",docOref,!
  write "%Doc Property value: ",docOref.%ToJSON()

By default, the %DocDB.Document.%Doc data type is %Library.DynamicAbstractObjectOpens in a new tab, which is the data type used to store a JSON object or a JSON array. You can specify a different data type in the CreateDatabase()Opens in a new tab method.

Other Database properties:

  • %DocDB.Document.%DocumentIdOpens in a new tab is an IDENTITY property containing a unique integer that identifies a document; %DocumentId counts from 1. In most cases, %DocumentId values are system-assigned. A %DocumentId must be unique; %DocumentIds are not necessarily assigned sequentially; gaps may occur in an assignment sequence. Document Database also automatically generates an IdKey index for %DocumentId values.

  • %DocDB.Document.%LastModifiedOpens in a new tab records a UTC timestamp when the Document instance was defined.

Using DocDB Databases with ObjectScript

The following sections describe how to use some important %DocDB.DatabaseOpens in a new tab methods:

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()
FeedbackOpens in a new tab