Querying nested objects in Cosmos DB

Question

I've looked at a number of answers and tried various approaches for querying nested objects I have in Cosmos DB. The database contains quite a large number of json type objects, of different types. I've been able to successfully query sub-objects of the first object in the list, but not subsequent objects.

The object itself looks like this:

{
    "id": "d44c1ff6-d5b0-41b0-b84c-2a6c20f99ada",
    "deviceUID": "10203040501020304051-8",
    "time": "2021-10-27T12:49:53.1174041Z",
    "connectorId": 1,
    "meterValues": {
        "eairo": 1013363236,
        "cio": 4
    },
    "hash": "8ADAED5BBF663AAFE93644CA071573906CC409F70231105F10C5CEE5AE8FC341"
}

And my connection and query are as follows:

cnxn: pyodbc.Connection = pyodbc.connect(
"""DRIVER={CData ODBC Driver for Cosmos DB};
   AccountEndpoint=x;
   AccountKey=y;""")
df = pd.read_sql_query("SELECT m.id, m.deviceUID, m.time, m.meterValues.eairo FROM metering m WHERE m.deviceUID <> null", cnxn)

It's the m.meterValues.eairo part of the SELECT which is proving problematic. The error reported is as follows:

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT m.id, m.deviceUID, m.time, m.meterValues.eairo FROM metering m WHERE m.deviceUID <> null': ('HY000', "[HY000] No table found with the alias 'meterValues'. (-1) (SQLExecDirectW)")

Any idea where I might be going wrong?

You don't need to say FROM metering m - just say FROM m. Aside from that: You should try using the native Python SDK for Cosmos DB, not an ODBC library (remember, Cosmos DB isn't a native relational database, and its SQL syntax is a subset of what you would find in a relational database). I suspect you'll have better results, as you're currently trying to coerce Cosmos DB into a relational db interface. — David Makogon
– David Makogon, Commented Oct 29, 2021 at 13:32

Matt · Accepted Answer · 2021-10-29 14:07:10Z

1

Credit to David for this, the SDK made light work of it.

Coded as follows:

from azure.cosmos import CosmosClient
import json

url = 'x'
key = 'y'
client = CosmosClient(url, credential=key)

database = client.get_database_client('z')
container = database.get_container_client('metering')

for item in container.query_items(
        query="SELECT m.id, m.deviceUID, m.time, m.meterValues.eairo FROM 
        metering m WHERE m.deviceUID <> null",
        enable_cross_partition_query=True):
    print(json.dumps(item, indent=True))

answered Oct 29, 2021 at 14:07

Matt

113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying nested objects in Cosmos DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related