CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Steve Drake
Steve Drake

Reputation: 2048

Cosmos equivalent of map / select

clients is an array inside my doc, the following query

SELECT 
f.id, f.clients
FROM f
where f.id ="35fb0733-dfa1-4932-9690-3ee5b05d89ff"

Returns

[
    "id": "35fb0733-dfa1-4932-9690-3ee5b05d89ff",
    {
        "clients": [
            {
                "firstname": "Benjamin",
                "surname": "Bob",
            },
            {
                "firstname": "Rachael",
                "surname": "Smith",
            }
        ]
    }
]

But I would like clients to look like :

"firstnames": [ "Benjamin", "Rachael" ]
"surnames": [ "Bob", "Smith" ]

Is this possible?

Upvotes: 0

Views: 668

Answers (2)

Jay Gong
Jay Gong

Reputation: 23782

Just give an additional option, you could use stored procedure to get the results you want.

function sample() {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT f.id, c.firstname,c.surname FROM f join c in f.clients where f.id ="1"',
    function (err, feed, options) {
        if (err) throw err;
        var map = {};
        var firstname = [];
        var surname =[];
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            for(var i=0;i<feed.length;i++){              
                map["id"] = feed[i].id;
                firstname.push(feed[i].firstname);
                surname.push(feed[i].surname);
            }
            map["firstname"] = firstname;
            map["surname"] =surname;

            var response = getContext().getResponse();
            response.setBody(map);
        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

Test Output:

{
    "id": "1",
    "firstname": [
        "Benjamin",
        "Rachael"
    ],
    "surname": [
        "Bob",
        "Smith"
    ]
}

Upvotes: 0

Samer Boshra
Samer Boshra

Reputation: 919

You could use the ARRAY expression w/ a subquery to achieve that.

Try this query:

SELECT
    ARRAY(SELECT VALUE client.firstname FROM client IN f.clients) AS firstnames,
    ARRAY(SELECT VALUE client.surname FROM client IN f.clients) AS surnames
FROM f

Upvotes: 1

Related Questions