Cosmos: Stop nesting results in an extra JSON object

[roji]

Our basic Cosmos query looks like this:

SELECT c FROM root c

This produces results such as the following:

[
    {
        "c": {
            "id": "...",
            "Foo": 1,
        }
    },
    {
        "c": {
            "id": "...",
            "Foo": 2,
        }
    }
]

Instead, we could generate the following simpler star-based query:

SELECT VALUE c FROM root c

Which produces:

[
    {
        "id": "...",
        "Foo": 1,
    },
    {
        "id": "...",
        "Foo": 2,
    }
]

[ajcvickers]

Note: breaking change because it impacts raw queries.

[roji]

This creates issues since subqueries need to project out the bare object, rather than an object wrapping the object (interferes with query). We could have different logic for top-level projections (which are processed by the shaper) and subquery projections (which aren't), but we really should just do the right thing.

[roji]

Example test for trivial scalar projection, NorthwindSelectQueryTestBase.Select_scalar:

public virtual Task Select_scalar(bool async)
    => AssertQuery(
        async,
        ss => ss.Set<Customer>().Select(c => c.City));

Currently-generated NoSQL:

SELECT c["City"]
FROM root c
WHERE (c["Discriminator"] = "Customer")

Cosmos results:

[
    {
        "City": "Berlin"
    },
    {
        "City": "México D.F."
    },
    {
        "City": "México D.F."
    },
    {
        "City": "London"
    },
    ...
]

Proposed NoSQL:

SELECT VALUE c["City"]
FROM root c
WHERE (c["Discriminator"] = "Customer")

Cosmos results:

[
    "Berlin",
    "México D.F.",
    "México D.F.",
    "London",
    ...
]

[roji]

And the same thing for a structural type projected out of the database, NorthwindSelectQueryTestBase.Select_customer_identity:

public virtual Task Select_customer_identity(bool async)
    => AssertQuery(
        async,
        ss => ss.Set<Customer>().Select(c => c));

Currently-generated NoSQL:

SELECT c
FROM root c
WHERE (c["Discriminator"] = "Customer")

Cosmos results:

[
    {
        "c": {
            "id": "Customer|ALFKI",
            ...
        }
    },
    {
        "c": {
            "id": "Customer|ANATR",
            ...
    },
    ....
]

Proposed NoSQL:

SELECT VALUE c
FROM root c
WHERE (c["Discriminator"] = "Customer")

Cosmos results:

[
    {
        "id": "Customer|ALFKI",
        ...
    },
    {
        "id": "Customer|ANATR",
        ...
    },
    ...
]

[roji]

Note: Cosmos automatically filters out undefined values. So if, for example, a list of strings is projected out, and we stop wrapping return values with objects (as is currently the case), then any undefined values will be filtered out. The wrapping with an object means that an empty object is returned instead, which allows us to know about the undefined result.

The undefined filtering behavior makes sense when you consider that JSON properties may or may not exist; for example, if you project out property Foo of all documents, and some documents don't have that property, then those documents would get naturally filtered out of the results. However, this is a bit more problematic in other cases where undefined is returned. For example, many functions return undefined when given a null argument, e.g. project out the first character of some property. This returns undefined where the property is null (not undefined!), and therefore would filter out those documents as well. This causes a behavioral divergence with the LINQ-to-Objects evaluation, where you'd get a NullReferenceExpression (but at least no silent dropping of data).

We discussed this with the Cosmos team and decided to keep the Cosmos "native" or expecetd behavior, i.e. to project out using VALUE despite the undefined filtering behavior. Wrapping with an object could be worse performance-wise, and is generally not the expected/natural way to query Cosmos.