DocumentDB query nested object

Question

I'm having trouble querying nested objects in DocumentDB. I have no control over the format of the data. Let's say an object looks like this in DocumentDB:

{
    "SCHEMA_ID": {
        "PROJECT": "A",
        "MODEL": "B",
        "GUID":"A GUID"
    },
    "STATE": {
        "Active": "True"
    },
    "OBJECTS": {
        "OBJECT": [
            {
                "ATTR_VALS": {
                    "NAME": "Header",
                    "ID": "0",
                    "VALUE": [
                        {
                            "NAME": "JobId",
                            "VAL": "1011656"
                        },
                        {
                            "NAM": "Region",
                            "VAL": "West Coast"
                        }
                    ]
                }
            },
            {
                "ATTR_VALS": {
                    "NAME": "SampleData",
                    "ID": "0",
                    "VALUE": [
                        {
                            "NAME": "Height",
                            "VAL": "5"
                        },
                        {
                            "NAM": "Length",
                            "VAL": "3"
                        }
                    ]
                }
            }
        ]
    }
}

I want to find all the objects that have a 'ATTR_VALS' = 'SampleData' and where those items have a 'Height'=5

So Far I have:

SELECT test.GUID
FROM test
join OBJECTS in test.OBJECTS
join OBJECT in OBJECTS
join ATTR_VALS in OBJECT
join VALUE in ATTR_VALS
WHERE ATTR_VALS.NAME = 'SampleData' AND VALUE.NAME='Height' AND   VALUE.VAL='5'

But this doesn't work, and returns no results. Thanks!

Do you get results when you click "Next Page"? Some queries might not return results in the first page(s), but will return results in subsequent pages. — Aravind Krishna R.
– Aravind Krishna R., Commented Jan 11, 2017 at 21:14
@AravindRamachandran I get 0 results back. I am running the query in .net code. Similar code with no joins returns data fine (like SELECT * FROM test WHERE test.GUID = 'A GUID') — Jeremy
– Jeremy, Commented Jan 11, 2017 at 21:18

Aravind Krishna R. · Accepted Answer · 2017-01-11 23:04:58Z

2

The query must be:

SELECT test.SCHEMA_ID.GUID
FROM test
join OBJ in test.OBJECTS.OBJECT
join VAL in OBJ.ATTR_VALS["VALUE"]
WHERE OBJ.ATTR_VALS.NAME = "SampleData" AND VAL.NAME='Height' AND VAL.VAL='5'

A couple things I changed:

JOIN must be performed against arrays, not objects. Objects can be expanded using the “.” Operator
VALUE is a special keyword and must be escaped
Small typo in the projection clause missing SCHEMA_ID

answered Jan 11, 2017 at 23:04

Aravind Krishna R.

8,00332 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

DocumentDB query nested object

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