Pier-Luc Gendreau Pier-Luc Gendreau - 4 months ago 26
SQL Question

Unwind an array in DocumentDB query

I have documents that look like this:

[
{
"id": "e1bb9b05-11f2-459e-37d3-9bf9fed56c96",
"name": "bulbasaur",
"type": [
{
"slot": 2,
"type": {
"url": "https://pokeapi.co/api/v2/type/4/",
"name": "poison"
}
},
{
"slot": 1,
"type": {
"url": "https://pokeapi.co/api/v2/type/12/",
"name": "grass"
}
}
]
}
]


The following query is about as close as I can get, but not quite the output I'm hoping for.

Query

SELECT
c.id, c.name, t.type.name as type
FROM
c
JOIN
t IN c.types
WHERE
c.name = "bulbasaur"


Result

[
{
"id": "e1bb9b05-11f2-459e-37d3-9bf9fed56c96",
"name": "bulbasaur",
"type": "poison"
},
{
"id": "e1bb9b05-11f2-459e-37d3-9bf9fed56c96",
"name": "bulbasaur",
"type": "grass"
}
]


Hoping for

[
{
"id": "e1bb9b05-11f2-459e-37d3-9bf9fed56c96",
"name": "bulbasaur",
"types": ["poison", "grass"]
}
]


Is this possible with a DocumentDB query?

Answer

This requires use of DocumentDB UDFs, which can extend query functionality with custom transformations. For example, register this:

  function unwindTypeArray(value) {
    var result = { id: value.id, name: value.name, types: []};
    for (var idx in value.type) {
      console.log(idx);
      var name = value.type[idx].type.name;
      result.types.push(name);
    }

    return result; 
}

Then call it inside a query like:

SELECT udf.unwindTypeArray(c) FROM c WHERE c.name = "bulbosaur"