CODEWITHSUNDEEP
sqlazure-cosmosdb
Pier-Luc Gendreau
Pier-Luc Gendreau

Reputation: 13814

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?

Upvotes: 0

Views: 583

Answers (1)

Aravind Krishna R.
Aravind Krishna R.

Reputation: 8003

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 = "bulbasaur"

Upvotes: 1

Related Questions