Aggregate multiple columns and rows into a json array



  • I try to query multiple columns and rows from a table to create the xy-pairs for a step-linechart in json format.

    I need the json like this:

    [[63115200,213.4], [139769280,213.4], [139769280,213.39], 
    [160911360,213.39], [160911360,215.1], [163853812,215.1]]
    

    What I currently get is this:

    [[[63115200,213.4], [139769280,213.4]], [[139769280,213.39],
    [160911360,213.39]], [[160911360,215.1], [163853812,215.1]]]
    

    I need to get rid of the square brackets json_build_array creates.

    The table looks like this:

    bname bfrom bto bval
    EU 63115200 139769280 213.4
    EU 139769280 160911360 213.39
    EU 160911360 163853812 215.1

    My query looks currently like this:

    SELECT   json_agg(json_build_array(array[bfrom,bval], array[bto,bval]) 
             ORDER BY bfrom asc) AS data
    FROM     stepchart 
    GROUP BY bname ORDER BY bname;
    

    I've been struggling with this for the last hours but still have no clue how to do this.

    I created a db-fiddle: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=168c9d68afc49c345d0dbac28cd670f3

    Hope someone can help me.



  • select json_agg(u.a) from (
      select
        bname, bfrom, json_build_array(bfrom,bval) a
        FROM   stepchart 
      union all
      select
        bname, bfrom, json_build_array(bto,bval)
        FROM   stepchart 
      order by bname, bfrom
    ) u;
    

    see https://dbfiddle.uk/?rdbms=postgres_14&fiddle=1991623d96797ad5738a7f18481c9410

    [[63115200, 213.4], [139769280, 213.4], [139769280, 213.39], [160911360, 213.39], [160911360, -9999.9], [163853812, -9999.9]]
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2