CODEWITHSUNDEEP
azurelinq-to-entitiesazure-cosmosdbazure-cosmosdb-sqlapi
user3501613
user3501613

Reputation: 610

Cosmos query to get the items inside list

I have a data structure like this:

{
  "commonId":"123",
  "Answers": [
    {
        "QuestionType": "1",
        "Answer": [
            "answer1"
        ]
    },
    {
        "QuestionType": "1",
        "Answer": [
            "answer2"
        ]
    },
    {
        "QuestionType": "2",
        "Answer": [
            "answer14"
        ]
    }
]
}

another file

 {
  "commonId":"123",
  "Answers": [
    {
        "QuestionType": "1",
        "Answer": [
            "answer3."
        ]
    },
    {
        "QuestionType": "1",
        "Answer": [
            "answer4."
        ]
    },
    {
        "QuestionType": "3",
        "Answer": [
            "answer24."
        ]
    }
]
}

Here i want to fetch only the Answer of QuestionType=1 with commonId:123 into a list of string. I tried below code but it is not working

string sqlQueryText = "SELECT c.Answers.Answer FROM c WHERE c.commonId=123 AND c.Answers.QuestionType=1";
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
FeedIterator<List<string>> queryResultSetIterator = cosmosContainer.GetItemQueryIterator<List<string>>(queryDefinition);
FeedResponse<List<string>> currentResultSet = await queryResultSetIterator.ReadNextAsync();

I am new to this azure-cosmosdb and currently i am fetching all answers of all question types and using linq i am filtering particular question answers.

Upvotes: 3

Views: 2230

Answers (1)

Anupam Chand
Anupam Chand

Reputation: 2687

You will need to use Joins. Your query will look like this.

SELECT t.Answer
FROM c
JOIN t IN c.Answers
WHERE t.QuestionType = "1" AND c.commonId = 123

Response is below.

[
    {
        "Answer": [
            "answer1."
        ]
    },
    {
        "Answer": [
            "answer2."
        ]
    },
    {
        "Answer": [
            "answer3."
        ]
    },
    {
        "Answer": [
            "answer4."
        ]
    }
]

You can have a look at this very good blog regarding iterating through arrays in Cosmos LINK.

Upvotes: 2

Related Questions