DocumentDB子查询 [英] DocumentDB Sub Query

问题描述

我正试图从一个包含双重嵌套数组的大型文档投影到该数组的展平表示中，而我对如何进行操作一无所知.

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.

我有与此相似的文件:

{
    "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")

但是，这将返回每种组件类型的单独记录.我试图将它们全部折叠在一起，以便所有组件都在包含文档的单个实例内.我希望能够执行类似嵌套SELECT的操作，在其中可以与外部文档连接，类似于以下内容:

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.

推荐答案

DocumentDB对子查询的支持，但当前不支持.同时，UDF或将数据客户端作为N条记录，然后重新格式化是当今最好的方法.对于其他感兴趣的人，这里有一个用于返回查询结果的UDF，

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子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！