3

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.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
user3501613
  • 596
  • 7
  • 28

1 Answers1

2

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.

Anupam Chand
  • 2,209
  • 1
  • 5
  • 14
  • i am getting null in "Answer" – user3501613 Sep 07 '21 at 07:35
  • 1
    Are your documents correct? You have an extra comma after "answer25." and "answer15." and the commonId is without quotes. And I'm assuming you actually have documents for the query. Can you update the question with your correct documents. – Anupam Chand Sep 07 '21 at 08:40
  • I updated the question but i cant upload my actual data here, thats why just changed the text, structure is same. – user3501613 Sep 07 '21 at 09:51
  • 1
    What is the value of commonId? Is it numeric 123 or a string "123" ? These 2 are different. If it is numeric the query will have c.commonId = 123 else it will be c.commonId = "123". – Anupam Chand Sep 07 '21 at 10:37
  • i given c.commonId = "123" as string only – user3501613 Sep 07 '21 at 11:10
  • 1
    But what is in your document? Is it "commonId":"123" or "commonId":123 ? In your question you have mentioned commonId:"123" which is not a valid JSON attribute. – Anupam Chand Sep 07 '21 at 11:17
  • it was a mystake, actual one is "commonId":"123". In my actual json also its the same – user3501613 Sep 07 '21 at 13:17