How to get all the members in an array in azure cosmosdb sql query?

Question

I have the following json in my container on azure cosmosdb:

    {
        "Data": {
            "High school": [
                {
                    "ClassData": [
                        {
                            "Teacher": "Hess Lyons",
                            "NumberOfStudents": "21",
                            "AverageScore": "82.9"
                        },
                        {
                            "Teacher": "Kristine Melendez",
                            "NumberOfStudents": "21",
                            "AverageScore": "91.0"
                        }
                    ]
                },
                {
                    "ClassData": [
                        {
                            "Teacher": "Lottie Poole",
                            "NumberOfStudents": "17",
                            "AverageScore": "88.6"
                        }
                    ]
                }
            ]
        }
    }

I would like to get all the pairs of 'NumberOfStudents' and 'AverageScore', where the 'NumberOfStudents' is 21. For example:

[
{
    "NumberOfStudents": "21",
    "AverageScore": "82.9"
},
{
    "NumberOfStudents": "21",
    "AverageScore": "91.0"
}
]

I just don't understand how to get all the members in the array. All I have for now is those queries:

1.select c.AllData.HighSchool[0].ClassData[0].NumberOfStudents,
         c.AllData.HighSchool[0].ClassData[0].AverageScore
    FROM c 
    where c.AllData.HighSchool[0].ClassData[0].NumberOfStudents = '21'

2.select results.NumOfStdnts_AvgScore[0].NumberOfStudents,
        results.NumOfStdnts_AvgScore[0].AverageScore from 
       (SELECT ARRAY(select classData.NumberOfStudents,
                    classData.AverageScore
                    from classData in highSchool.ClassData) as NumOfStdnts_AvgScore
     FROM highSchool in items.AllData.HighSchool) as results
where results.NumOfStdnts_AvgScore[0].NumberOfStudents = '21'

But these queries only return the first member in each array. I would really appreciate any help, Thanks!

Gal Itzhak · Accepted Answer · 2019-08-14 17:00:28Z

1

I've been able to get this right using:

select
    p.NumberOfStudents,
    p.AverageScore
 FROM f
    JOIN c IN f.AllData.HighSchool
    JOIN p IN c.ClassData
where p.NumberOfStudents = '21'

answered Aug 14, 2019 at 17:00

Gal Itzhak

5191 gold badge8 silver badges14 bronze badges

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

1 Comment

not2qubit Over a year ago

Where's AllData coming from? (Doesn't match with your JSON.)

Collectives™ on Stack Overflow

How to get all the members in an array in azure cosmosdb sql query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related