Aggregate multiple columns and rows into a json array

Question

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.

• kerniebc: sorry for the table design. In preview it works.
• Gerard H. Pille: your fiddle differs from the data you show here.

Answer 1

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 dbfiddle.uk

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