Azure Cosmos DocumentDB - return array from 1 property in sub array

Question

I've searched around to see if anybody needed this before but couldn't find much.

I have the following json objects in Azure DocumentDB collection:

{
    personID: 1,
    name: "Bruce",
    surname: "Dickinson",
    items: [
        {
            itemID: 1,
            itemType: "A",
            name: 'Item 1'
        },
        {
            itemID: 2,
            itemType: "A",
            name: 'Item 2'
        },
        {
            itemID: 3,
            itemType: "B",
            name: 'Item 3'
        }
    ]

}

The collection has many Persons in it. personID 2, 3, 4 ......100....1000 and so on.

I would like to write a SQL query to extract an array of itemIDs for personID = 1 and items with itemType = 'A'. I want the result to look like this:

itemIDs: [
    1,
    2
]

or simply:

Has anybody ever tried doing this before? Is it even possible?

Larry Maccherone · Accepted Answer · 2017-09-11 13:40:52Z

1

Someone with more SQL foo than me might know a way to do it with just SQL, but I would use a user-defined function (UDF) to do it.

Here is the SQL:

SELECT VALUE udf.getItemIDArray(c) FROM collection c WHERE <your clause>

Here's what the UDF might look like:

function getItemIDArray (o) {
  output = [];
  for (item in o.items) {
    output.push(item.itemID)
  }
  return output 
}

edited Sep 11, 2017 at 13:40

answered Sep 11, 2017 at 11:24

Larry Maccherone

9,5433 gold badges30 silver badges43 bronze badges

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

3 Comments

ZeroOne Over a year ago

Mmmm interesting. Thanks for that. The output.push(item.itemID) seems to be pushing a null instead of the item.itemID. Why would that be you think?

Larry Maccherone Over a year ago

Hmm try putting a "var" in front of "for (var item in o.items)"

ZeroOne Over a year ago

Still did not work. I am doing it in the application for the time being but would like to do it at the db level to transport less over the network.

Collectives™ on Stack Overflow

Azure Cosmos DocumentDB - return array from 1 property in sub array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related