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

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

How to alias array elements in CosmosDB documents?

问题

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

I have the following document,

  1. {
  2.         "VehicleDetailId": 1,
  3.         "VehicleDetail": [
  4.             {
  5.                 "Id": 1,
  6.                 "Make": "BMW"
  7.             },
  8.             {
  9.                 "Id": 1,
  10.                 "Model": "ABDS"
  11.             },
  12.             {
  13.                 "Id": 1,
  14.                 "Trim": "5.6L/ASMD"
  15.             },
  16.             {
  17.                 "Id": 1,
  18.                 "Year": 2008
  19.             }
  20.         ]
  21. }

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

  1. {
  2.         "VehicleDetailId": 1,
  3.         "Type": "VehicleDetail",
  4.         "VehicleDetail": [
  5.             {
  6.                 "MakeId": 1,
  7.                 "MakeValue": "BMW"
  8.             },
  9.             {
  10.                 "ModelId": 1,
  11.                 "ModelValue": "ABDS"
  12.             },
  13.             {
  14.                 "TrimId": 1,
  15.                 "TrimValue": "5.6L/ASMD"
  16.             },
  17.             {
  18.                 "YearId": 1,
  19.                 "YearValue": 2008
  20.             }
  21.         ]
  22. }

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

  1. SELECT c.vehicleDetailId, ARRAY(SELECT v.Id AS MakeId, v.Make AS MakeValue,
  2.                                        v.Id AS ModelId, v.Model AS ModelValue,
  3.                                        v.Id AS TrimId, v.Trim AS TrimValue,
  4.                                        v.Id AS YearId, v.Year AS YearValue
  5.                                 FROM v IN c.VehicleDetail) AS VehicleDetail 
  6. 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 代码：

  1. function userDefinedFunction(array){
  2.     var returnArray = [];
  3.     for(var i=0;i<array.length;i++){
  4.         var obj = array[i];
  5.         var map = {};
  6.         if(obj.Make){
  7.             map["MakeId"]= obj.Id;
  8.             map["MakeValue"]= obj.Make;
  9.         }else if(obj.Model){
  10.             map["ModelId"]= obj.Id;
  11.             map["ModelValue"]= obj.Model;
  12.         }else if(obj.Trim){
  13.             map["TrimId"]= obj.Id;
  14.             map["TrimValue"]= obj.Trim;
  15.         }else if(obj.Year){
  16.             map["YearId"]= obj.Id;
  17.             map["YearValue"]= obj.Year;
  18.         } 
  19.         returnArray.push(map);
  20.     }
  21.     return returnArray;
  22. }

SQL：

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

输出：

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

英文:

You could use UDF to implement your needs.

Udf code:

  1. function userDefinedFunction(array){
  2.     var returnArray = [];
  3.     for(var i=0;i&lt;array.length;i++){
  4.         var obj = array[i];
  5.         var map = {};
  6.         if(obj.Make){
  7.             map[&quot;MakeId&quot;]= obj.Id;
  8.             map[&quot;MakeValue&quot;]= obj.Make;
  9.         }else if(obj.Model){
  10.             map[&quot;ModelId&quot;]= obj.Id;
  11.             map[&quot;ModelValue&quot;]= obj.Model;
  12.         }else if(obj.Trim){
  13.             map[&quot;TrimId&quot;]= obj.Id;
  14.             map[&quot;TrimValue&quot;]= obj.Trim;
  15.         }else if(obj.Year){
  16.             map[&quot;YearId&quot;]= obj.Id;
  17.             map[&quot;YearValue&quot;]= obj.Year;
  18.         } 
  19.         returnArray.push(map);
  20.     }
  21.     return returnArray;
  22. }

Sql:

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

Output:

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

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

发表评论

匿名网友

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

确定