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: link

Hope someone can help me.

  • sorry for the table design. In preview it works.
    – kerniebc
    Feb 10, 2022 at 16:54
  • 1
    your fiddle differs from the data you show here. Feb 10, 2022 at 18:21

1 Answer 1

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

see dbfiddle.uk

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.