0

I have an table like this:

enter image description here

What i want to achive is to group it by its point_delivery_number and the other data should be grouped into an array. The result should be like this:

point_delivery_number       |     data 
----------------------------------------
AT2341234asdf5234452341243  | [
                            |  { year: "2021-01...", month: "..", consumption: "..", generation: "...", self_coverage: "..." },
                            |  { year: "2021-01...", month: "..", consumption: "..", generation: "...", self_coverage: "..." },
                            |  ...
                            | ]
----------------------------------------
AT523452345sadf345          | [{ ... }]

Is this even possible with SQL?

I am using AWS Timestream database, so there are some limitations. Certain functions are not even supported. You can see what is supported:

https://docs.aws.amazon.com/timestream/latest/developerguide/reference.html

Shadow
  • 33,525
  • 10
  • 51
  • 64
bill.gates
  • 14,145
  • 3
  • 19
  • 47
  • Please pay attention to the tags you use as it can mislead those, who would like to help you and will also waste your time as the answers may not work for you! Mysql is a completely different rdbms product, which has absolutely nothing to do with your question! – Shadow May 24 '22 at 14:00
  • @Shadow i am sorry, i am new to it. I thought the underlying tech is MySQL but its indeed not. – bill.gates May 24 '22 at 14:02

1 Answers1

1

You can use JSON_ARRAY_AGG combined with JSON_OBJECT:

SELECT point_delivery_number, JSON_ARRAYAGG(JSON_OBJECT(
    'year', year,
    'month', month,
    'self_coverage', self_coverage
)) AS data
FROM t
GROUP BY point_delivery_number
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Thank you, but unfortunetaly, `JSON_ARRAYAGG` or `JSON_OBJECT` is not supported by AWS Timestream database so it makes things more difficult. Here are the functions etc. wich are supported by AWS https://docs.aws.amazon.com/timestream/latest/developerguide/reference.html maybe there is something different to group things together. Maybe there is an other solution to group it? – bill.gates May 24 '22 at 13:48