New post
Question
· Sep 16, 2023

DocDB: Can you find a document based on array contents?

#Document Data Model (NoSQL) #InterSystems IRIS for Health

Hello all,

Using the example from the DocDB documentation below, is there a way to create a property and form a predicate that would be able to find a document where the phone number is a cell number and the number is "401-123-4567"?

From Introducing InterSystems IRIS Document Database (DocDB)

  SET dynAbObj = {
   "FullName":"John Smith",
   "FirstName":"John",
   "Address":{
              "street":"101 Main Street",
              "city":"Mapleville",
              "state":"NY",
              "postal code":10234
             },
   "PhoneNumber":
              [
               {"type":"home","number":"212-456-9876"},
               {"type":"cell","number":"401-123-4567"},
               {"type":"work","number":"212-444-5000"}
              ]
  }
  SET jstring = dynAbObj.%ToJSON() // dynamic abstract object to JSON string
  DO personDB.%FromJSON(jstring)   // JSON string inserted into document database
Discussion (5)2
Log in or sign up to continue
Jonathan Lent · Sep 16, 2023

Right, I understand the predicate aspect of FindDocuments(), but I understood that any value used in a predicate must first be created as a property with CreateProperty(). The doc for that method is sparse, and I have not quite grok'd how a dynArry would play into the expression. Creating a property for "FullName" is pretty self-explanatory, especially since it is at the root of the hierarchy. I've also seen an example in the forums where a property was created using a specific index in an array -- which would not be terribly useful for an unordered collection. Assuming that the person object in the example is the document in the DocDB and the PhoneNumber array above has no particular order, what would be the expression used in CreateProperty() for "type" and/or "number??

I think once I see how that particular aspect is supposed to work, the rest of it is easy.

Thank you all for taking the time to offer help.

0 0
Luis Angel Pére... · Sep 18, 2023

You are right, you have to create the properties before to use %FindDocuments...BUT...it doesn't work for arrays of data. I've found this answer to a similar question:

https://community.intersystems.com/post/how-query-data-docdb-through-pyt...

I was testing the behaviour with this example:

  DO db.%CreateProperty("type","%String","$.PhoneNumber.type")
    

  SET dynAbObj = {
   "FullName":"John Smith",
   "FirstName":"John",
   "Address":{
              "street":"101 Main Street",
              "city":"Mapleville",
              "state":"NY",
              "postal code":10234
             },
   "PhoneNumber":
              [
               {"type":"home","number":"212-456-9876"},
               {"type":"cell","number":"401-123-4567"},
               {"type":"work","number":"212-444-5000"}
              ]
  }
  $$$TRACE(dynAbObj.%ToJSON())
  SET jstring = dynAbObj.%ToJSON() // dynamic abstract object to JSON string
  DO db.%FromJSON(jstring)   // JSON string inserted into document database
  $$$TRACE(db.%FindDocuments(["type","cell","="]).%ToJSON())
  $$$TRACE(db.%FindDocuments(["type","home","="]).%ToJSON())

This is the answer for the first search:

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

And this is the answer for the second search:

{"sqlcode":100,"message":null,"content":[{"%Doc":"{\"FullName\":\"John Smith\",\"FirstName\":\"John\",\"Address\":{\"street\":\"101 Main Street\",\"city\":\"Mapleville\",\"state\":\"NY\",\"postal code\":10234},\"PhoneNumber\":[{\"type\":\"home\",\"number\":\"212-456-9876\"},{\"type\":\"cell\",\"number\":\"401-123-4567\"},{\"type\":\"work\",\"number\":\"212-444-5000\"}]}","%DocumentId":"1","%LastModified":"2023-09-18 10:13:30.694"}]}

As you can see, it only works fine for the first value of the array, the definition of the property doesn't allow to define an array of values.

1 0