Azure Cosmos DB 将相关实体添加到查询结果中

Question

在我的 cosmos DB 集合中，我有此类类型的实体:

// Suppliers:

[{
    "id": "some_unique_str",
    "products": [
        "id_of_product1",
        "id_of_product2"
    ]
}]

// Products: 

[{
    "id": "id_of_product1",
    "name": "product name"
},
//...
]

我需要编写一个查询才能获得这样的结果:

[{
    "id": "some_unique_str",
    "products": [
        {
            "id": "id_of_product1",
            "name": "product 1 name"
        },
        {
            "id": "id_of_product2",
            "name": "product 2 name"
        }
    ]
}]

换句话说:我正在尝试实现 OData 扩展功能。可能吗？

Answer 1

您无法使用 cosmos db sql api 中的直接 sql 来实现这一点。您的需求可以在关系数据库中使用外键来实现，而不是no-sql数据库。

在cosmos db sql api中，可以通过存储过程来实现。我尝试写了一些代码供大家引用:

function sample(prefix) {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT c.id,c.products FROM c where is_defined(c.products)',
    function (err, feed, options) {
        if (err) throw err;

        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            var response = getContext().getResponse();
            var returnArray = [];
            for (var i=0;i<feed.length;i++){  
                var map = {};
                map['id'] = feed[i].id;
                mergeData(map,feed[i].products);
                returnArray.push(map);
            }
            response.setBody(returnArray);
        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');

    function mergeData(map,idArray){
        var sqlQuery = {
            "query":  'SELECT c.id,c.name FROM c where not is_defined(c.products) and '+
             ' array_contains( @idArray,c.id,true) ',
            "parameters": [
                {"name": "@idArray", "value": idArray}
            ]
        }
        var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        sqlQuery,
        function (err, feed, options) {
        if (err) throw err;

        if (!feed || !feed.length) {
            map['products'] = [];
        }
        else {
           map['products'] = feed;
        }
    });
    }
}

示例数据的输出是: