how to filter array of json values in cosmosdb

Question

my sample document looks like
{
"id": "edefe0c5-7c5f-468c-90fc-95ff362124ef",
"docType":"testDoc",
"cards":[
{
"cnumber":"123445",
"cstatus":"Active"

},
{
"cnumber":"123445",
"cstatus":"Cancel"
}
]
}

Possible cstatus are Active, Issue, Cancel, Compromise
I am trying to get Id's with Cancel or Compromise cstatus only.
The query I have tried is giving me the results which include above sample Id also which has cstatus Active in one of the card

SELECT top 10 c.Id,c.cards
FROM c JOIN t IN c.cards WHERE (t.status='Cancel' OR t.status = 'Compromise')

Answer 1

Hi, @Mohan Krishna Bhimanadam Welcome to the Microsoft Q&A platform, thanks for posting the question.

You can try the following query, This query will only return the Id's where the cstatus is either Cancel or Compromise

SELECT top 10 c.Id,c.cards FROM c WHERE ARRAY_CONTAINS(c.cards, {cstatus: "Cancel"}) OR ARRAY_CONTAINS(c.cards, {cstatus: "Compromise"})

Please refer to this doc here

Please let me know if this works.

Regards

Geetha

Answer 2

Hi @Mohan Krishna Bhimanadam,

Thanks for posting question in Microsoft Q&A forum.

As I understand, you are trying to query to get Id's with Cancel or Compromise cstatus only from the sample document.

The query used is not able to filter cstatus required and giving all values in result.

Please use below query:

select c.id,
  ARRAY(
SELECT x.cstatus,x.cnumber FROM x in c.cards
where x.cstatus!='Active') as cards
from c

Here is the result:

You may use ARRAY Projection concept from the link Understanding how to query arrays in Azure Cosmos DB

Hope this helps. Let me know if you have further queries.

If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.