Question
Wenqing Wu · Feb 24, 2021

How to query data from DocDB through python based on the value in nested json data?

hello, I had build a DocDB referring the code from here: https://community.intersystems.com/post/how-can-i-create-properties-docd...

Here are the structure of the json Documents I have added in my database called 'Orders'.

Doc1 = {'Client': 'Stanford health center', 'Country': 'US', 'orderID': 'a0001', 'Date': '2020-01-01',
        'OrderContent': [{'productID': '001', 'ProductName': 'flu test', 'Qty': 100, 'cost': 500},
                         {'productID': '002', 'ProductName': 'EV test', 'Qty': 500, 'cost': 1000}]}

Doc2 = {'Client': 'watson clinic', 'Country': 'US', 'orderID': 'a0002', 'Date': '2020-03-01',
        'OrderContent': [{'productID': '001', 'ProductName': 'flu test', 'Qty': 200, 'cost': 1000},
                         {'productID': '003', 'ProductName': 'HPV test', 'Qty': 90, 'cost': 700}]}

Doc3 = {'Client': 'WHO', 'Country': 'FR', 'orderID': 'a0003', 'Date': '2020-01-01',
        'OrderContent': [{'productID': '004', 'ProductName': 'C_diff test', 'Qty': 100, 'cost': 500},
                         {'productID': '002', 'ProductName': 'EV test', 'Qty': 500, 'cost': 1000}]}

Like you can see, I am struggling at how to retrieve the document based on 'productID' as the key in 'OrderContent'? Currently I can retrieve data based on properties as 'Client', "Country", "orderID", "Date", but I have no idea how to setup properties like 'OrderContent', and the keys within the nested json format in order to retrieve the document.

And if I have more complex logic such as getting the document where country is 'US' and containing cost higher than 600, how can I query the data?  Hope you may help! Thank you ahead!

Following is the code that I used for retrieving the data based on property 'orderID'

def find_doc_by_logic(data):
    headers = {
        'Content-Type': 'application/json',
    }

    # data = '{"restriction":["orderID","a","%STARTSWITH"], "projection":["%DocumentId", "Client"]}'
    response = session.post('http://localhost:52773/api/docdb/v1/User/find/Orders', headers=headers, data=data)
    print(response.status_code, response.reason)
    print(response.content)

# testing work, return the document contain the 'client' starting with 'c'
data = '{"restriction":["Client","c","%STARTSWITH"], "projection":["%DocumentId", "Client", "Country"]}'
find_doc_by_logic(data)

 

 

00
1 0 1 81
Log in or sign up to continue

Replies

I don't know whether this is documented, but you can use array accessors in the property expression. To create a property for the productID of the first entry in the OrderContent array, you should be able to use an expression like this in a %CreateProperty() call: $.OrderContent[0].productID.

Unfortunately, this doesn't address the general case of searching an array. I don't think you can create a collection property in DocDB, nor do the %FindDocuments() operators seem to be of much use. You might try poking around in your generated class to see if you can use an existing property as a template for creating your own computed property that aggregates the productID. If that works, you may still find the %FindDocuments() operators to be inadequate, but the property would then be accessible to SQL queries.