英文:
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
输出:
英文:
You could use UDF to implement your needs.
Udf code:
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
Output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论