1

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.

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

1 Answer 1

Reset to default
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]]
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.