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

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.