DocumentDB SQL ARRAY_CONTAINS

Question

In this case, the documents look like:

{
 "id" : "1",
 "Properties" : [
  {
   "Name" : "Leonard",
   "Result" : "Pass",
   "Grade" : "A"
  },
  {
   "Name" : "Sheldon",
   "Result" : "Pass",
   "Grade" : "A"
  },
  {
   "Name" : "Raj",
   "Result" : "Fail",
   "Grade" : "F"
  },
  {
   "Name" : "Howard",
   "Result" : "Pass",
   "Grade" : "B"
  }
 ]
}

I want to write a query which returns the elements with the "Result" : "Pass". I have tried ARRAY_CONTAINS() with query

SELECT * FROM d as c WHERE ARRAY_CONTAINS(c.Result, {"Result" : "Pass"}, true)

The Problem with the query is that it returns the whole of the array in as it is able to find "Result": "Pass" in the array. How should I modify or change the query such that the result contains only those elements of the array which has "Result": "Pass".

Sajeetharan · Accepted Answer · 2019-12-26 07:42:07Z

1

You just need to select the Properties

SELECT  c.Properties[0] as Result
FROM c
JOIN tag IN c.Properties
WHERE tag.Result = "Pass"

answered Dec 26, 2019 at 7:42

Sajeetharan

223k65 gold badges370 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

learner Over a year ago

Apparently, the query you suggested is also returning the whole array if one of the items of the array has "Result" : "Pass". I need only those items from the array which has "Result" : "Pass".

Sajeetharan Over a year ago

Not really, it returns only those object which has result pass.

Bob Ash · Accepted Answer · 2019-12-31 07:43:01Z

0

Try this query which will return the id and only the matching items from the Properties array.

SELECT d.id, property FROM d JOIN property IN d.properties WHERE property.Result= "Pass"

answered Dec 31, 2019 at 7:43

Bob Ash

89510 silver badges12 bronze badges

Collectives™ on Stack Overflow

DocumentDB SQL ARRAY_CONTAINS

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related