group data by same timestamp using cosmos db sql

Question

Question: I am trying SQL query as the image showed below，I want it to be grouped by the same timestamp

expected output:

[  
   {
      "tag1": {
        "TagName": "PV1-input-power-L(10W)",
        "Value": 0
       },
       "tag2": {
        "TagName": "Sunshine-Display-Value",
        "Value": 0
       },
       "tag3": {
        "TagName": "TotalEnergy-(100kWh)_1",
        "Value": 0
       },
      "timestamp": "2020-03-27T02:40:18Z"
    }
 ]

sample document:

I think this will be easier to accomplish if you will create a sproc. — Hasan Savran
– Hasan Savran, Commented Apr 6, 2020 at 13:25
@HasanSavran My Collection is kinda unusal due to IoThub 4K file limit , one document is split into two. As you refer sproc , just wonder if it can make two document into one before any other operation? — FVBn
– FVBn, Commented Apr 7, 2020 at 1:31

Hasan Savran · Accepted Answer · 2020-04-07 14:40:41Z

1

You can use User Defined Functions.

Here is the data from my containers

Here is the function I have created. I named it CustomArray.

function userDefinedFunction(input){
var obj={};
input.forEach(function(element,index){ 
obj["tag"+index] = {
    TagName :element.TagName,
    Value: element.Value
};
}); return obj;}

Here, I run the UDF with my select statement

It returns the following data.

Schema is very close to what you are looking for. I think you can make it better by changing some jscript in UDF. I hope this helps!

answered Apr 7, 2020 at 14:40

Hasan Savran

3932 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

group data by same timestamp using cosmos db sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related