Postgresql array to json-CodePudding

I have a table like:

id	time_serie	value
1	2020-09-25 00:00:00	100
1	2020-09-25 00:10:00	200
1	2020-09-25 00:20:00	300
1	2020-09-25 00:30:00	400

I want a JSON output as:

{
  "ID": 1,
  "time_serie": [
    {
      "position": 1,
      "inQuantity": 100
    },
    {
      "position": 2,
      "inQuantity": 200
    },
    {
      "position": 3,
      "inQuantity": 300
    },
    {
      "position": 4,
      "inQuantity": 400
    }
  ...
  ]
}

Thanks

CodePudding user response：

You can use a mix of JSON functions along with ROW_NUMBER() window function in order to generate positions such as

SELECT *
  FROM
  (
   SELECT JSON_BUILD_OBJECT('ID', id, 
                            'time_serie',
                    JSON_AGG(
                             JSON_BUILD_OBJECT('position',id,'inQuantity',value)
                            )
                           )        
     FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY time) AS rn FROM t) AS t
    GROUP BY id 
   ) AS j

Demo