DocumentDB Sub Query

Question

I am trying to project from a large document containing a double nested array, into a flattened representation of the array, and I am stuck on how to proceed.

I have documents similar to this:

{
    "id": "1",
    "themeId": "e4d3549c-2785-4067-83d6-f396d2212776",
    "enabled": false,
    "archived": false,
    "componentGroups": [
      [
        {
          "componentType": "header",
          "enabled": true,
          "configurationVariables": {
            "text1": "AAA",
            "text2": "BBB"
          }
        }
      ],
      [
        {
          "componentType": "prompt",
          "enabled": true,
          "configurationVariables": {
            "text1": "AAA",
            "text2": "BBB"
          }
        },
        {
          "componentType": "proactive",
          "enabled": true,
          "configurationVariables": {
            "text1": "AAA",
            "text2": "BBB"
          }
        }
      ],
      [
        {
          "componentType": "product-feed",
          "enabled": true,
          "configurationVariables": {
            "text1": "AAA",
            "text2": "BBB"
          }
        }
      ]
    ]
  }

I am trying to project it to the following structure:

{
    "id": "275973",
    "themeId": "e4d3549c-2785-4067-83d6-f396d2212776",
    "enabled": false,
    "archived": false,
    "components": [
        {
          "componentType": "header",
          "enabled": true
        },
        {
          "componentType": "prompt",
          "enabled": true,
        },
        {
          "componentType": "proactive",
          "enabled": true,
        },
        {
          "componentType": "product-feed",
          "enabled": true
        }
      ]
    ]
  }

I've had some success using the following query:

SELECT T.id, 
    T.themeId, 
    T.enabled, 
    T.archived, 
    [ { type: C.componentType, enabled: C.enabled } ] AS components
FROM Panels T 
JOIN CG IN T.componentGroups
JOIN C IN CG
WHERE T.id IN ("275973")

However this returns a separate record for each component type. I am trying to fold them all together so that all the components are inside a single instance of the containing document. I was hoping to be able to do something like a nested SELECT where I can join with the outer document, similar to this:

SELECT T.id, 
T.themeId,
T.enabled, 
T.archived, 
[ 
    SELECT C.componentType, C.enabled
    FROM CG IN T.componentGroups
    JOIN C IN CG 
] AS components
FROM Panels T
WHERE T.id IN ("275973")

But this is invalid. I'm looking for information on sub / nested selects and returning data by drilling into nested arrays.

Answer 1

DocumentDB support for sub-queries is planned, but not currently supported. Meanwhile, UDFs or pulling the data client side as N records, then re-formatting is the best way to do this today. For others interested, here's a UDF for returning the results in the query,

function transform(doc) {
    var result = {};

    for (var prop in doc) {
        if (prop != "componentGroups") {
            result[prop] = doc[prop];
        } else {
            result["components"] = [];
            for(var cgidx in doc["componentGroups"]) {
                var componentGroup = doc["componentGroups"][cgidx];
                for (var cidx in componentGroup) {
                    var component = componentGroup[cidx];
                    result["components"].push({componentType: component.componentType, enabled: component.enabled });
                }
            }
        }
    }

    return result;
 }