如何在CosmosDB文档中为数组元素设置别名?

huangapple go评论71阅读模式
英文:

How to alias array elements in CosmosDB documents?

问题

SELECT c.VehicleDetailId,
       ARRAY(SELECT v.Id AS MakeId, v.Make AS MakeValue,
                   v.Id AS ModelId, v.Model AS ModelValue,
                   v.Id AS TrimId, v.Trim AS TrimValue,
                   v.Id AS YearId, v.Year AS YearValue
            FROM v IN c.VehicleDetail) AS VehicleDetail 
FROM c
英文:

I have the following document,

{
        "VehicleDetailId": 1,
        "VehicleDetail": [
            {
                "Id": 1,
                "Make": "BMW"
            },
            {
                "Id": 1,
                "Model": "ABDS"
            },
            {
                "Id": 1,
                "Trim": "5.6L/ASMD"
            },
            {
                "Id": 1,
                "Year": 2008
            }
        ]
}

I want to give aliases for the array elements, something like this,

{
        "VehicleDetailId": 1,
        "Type": "VehicleDetail",
        "VehicleDetail": [
            {
                "MakeId": 1,
                "MakeValue": "BMW"
            },
            {
                "ModelId": 1,
                "ModelValue": "ABDS"
            },
            {
                "TrimId": 1,
                "TrimValue": "5.6L/ASMD"
            },
            {
                "YearId": 1,
                "YearValue": 2008
            }
        ]
}

The following query seems to work fine, but since Id is common for all, it is repeating every time.

SELECT c.vehicleDetailId, ARRAY(SELECT v.Id AS MakeId, v.Make AS MakeValue,
                                       v.Id AS ModelId, v.Model AS ModelValue,
                                       v.Id AS TrimId, v.Trim AS TrimValue,
                                       v.Id AS YearId, v.Year AS YearValue
                                FROM v IN c.VehicleDetail) AS VehicleDetail 
FROM c

How should I write the query so that the Id does not repeat every time, and I can fetch an element from a specific position?

答案1

得分: 1

你可以使用用户自定义函数(UDF)来实现你的需求。

UDF 代码:

function userDefinedFunction(array){
    var returnArray = [];
    for(var i=0;i<array.length;i++){
        var obj = array[i];
        var map = {};
        if(obj.Make){
            map["MakeId"]= obj.Id;
            map["MakeValue"]= obj.Make;
        }else if(obj.Model){
            map["ModelId"]= obj.Id;
            map["ModelValue"]= obj.Model;
        }else if(obj.Trim){
            map["TrimId"]= obj.Id;
            map["TrimValue"]= obj.Trim;
        }else if(obj.Year){
            map["YearId"]= obj.Id;
            map["YearValue"]= obj.Year;
        } 
        returnArray.push(map);
    }
    return returnArray;
}

SQL:

SELECT c.VehicleDetailId, udf.test(c.VehicleDetail) AS VehicleDetail 
FROM c

输出:

如何在CosmosDB文档中为数组元素设置别名?

英文:

You could use UDF to implement your needs.

Udf code:

function userDefinedFunction(array){
    var returnArray = [];
    for(var i=0;i&lt;array.length;i++){
        var obj = array[i];
        var map = {};
        if(obj.Make){
            map[&quot;MakeId&quot;]= obj.Id;
            map[&quot;MakeValue&quot;]= obj.Make;
        }else if(obj.Model){
            map[&quot;ModelId&quot;]= obj.Id;
            map[&quot;ModelValue&quot;]= obj.Model;
        }else if(obj.Trim){
            map[&quot;TrimId&quot;]= obj.Id;
            map[&quot;TrimValue&quot;]= obj.Trim;
        }else if(obj.Year){
            map[&quot;YearId&quot;]= obj.Id;
            map[&quot;YearValue&quot;]= obj.Year;
        } 
        returnArray.push(map);
    }
    return returnArray;
}

Sql:

SELECT c.VehicleDetailId,udf.test(c.VehicleDetail) AS VehicleDetail 
FROM c

Output:

如何在CosmosDB文档中为数组元素设置别名?

huangapple
  • 本文由 发表于 2020年1月6日 16:00:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/59608549.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定