json - Cosmos DB documents result in an array

Question

Suppose I am querying Cosmos DB with the following query (SELECT c.Name, c.Job FROM C) an having the following 2 documents as a result:

[{
"Name":"test",
"Job" : "Hello"
},
{
"Name":"test2",
"Job" : "Hello2"
}]

I would like to have the results in a array for every attribute like following:

[{
"Name" : ["test", "test2"],
"Job" : ["Hello", "Hello2"]
}]

I would like to know if this is possible in cosmos db and if anyone has experience with this. I would appreciate it!

Thank you!

Answer 1

It's impossible to get your expected output with one SQL in Cosmos DB. You need to make two call:

SELECT 
    value c.Name
FROM c

AND

SELECT 
    value c.Job
FROM c