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.

To make sure I understand the question, you want to drop the configurationVariables field from the sub-elements and flatten the components list, right? Also, there are a number of other fields in your expected output that don't appear in the document example. Are those already at the top level? The answer I intend to provide is a UDF that will do the "projection" for you. I suspect that what you are suggesting can be done in pure SQL but I find that JavaScript UDFs are a much easier way to accomplish these transformations than using SQL. — Larry Maccherone
Hi Larry. Corrected the projected document. And yes, you are correct in what I am looking to achieve. I am trying to avoid UDF's at the moment as I don't have a good strategy for managing them as I'd expect it to by synced up at app start but the apps are deployed to a large number of machines, so I can see this causing problems. — Tristan Rhodes
I have a master node in my system that reloads all Sprocs and UDFs every time it boots up. I can also trigger something to do it whenever I wish. But if you really want to avoid UDFs, then I don't know how to do what you are asking. Someone else may though. I've seen some SQL masters answer these questions. — Larry Maccherone

Aravind Krishna R. Aravind Krishna R. · Accepted Answer · 2016-09-06T18:22:10

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;
 }

DocumentDB Sub Query

1 Answers