Combine JSONB array of values by consecutive pairs-CodePudding

In postgresql, I have a simple one JSONB column data store:

data
----------------------------
{"foo": [1,2,3,4]}
{"foo": [10,20,30,40,50,60]}
...

I need to convert consequent pairs of values into data points, essentially calling the array variant of ST_MakeLine like this: ST_MakeLine(ARRAY(ST_MakePoint(10,20), ST_MakePoint(30,40), ST_MakePoint(50,60))) for each row of the source data.

Needed result (note that the x,y order of each point might need to be reversed):

data                          geometry (after decoding)
----------------------------  --------------------------
{"foo": [1,2,3,4]}            LINE (1 2, 3 4)
{"foo": [10,20,30,40,50,60]}  LINE (10 20, 30 40, 50 60)
...

Partial solution

I can already iterate over individual array values, but it is the pairing that is giving me trouble. Also, I am not certain if I need to introduce any ordering into the query to preserve the original ordering of the array elements.

SELECT ARRAY(
   SELECT elem::int
   FROM jsonb_array_elements(data -> 'foo') elem
) arr FROM mytable;

CodePudding user response：

You can achieve this by using window functions lead or lag, then picking only every second row:

SELECT (
  SELECT array_agg((a, b) ORDER BY o)
  FROM (
    SELECT elem::int AS a, lead(elem::int) OVER (ORDER BY o) AS b, o
    FROM jsonb_array_elements(data -> 'foo') WITH ORDINALITY els(elem, o)
  ) AS pairs
  WHERE o % 2 = 1
) AS arr
FROM example;

^{(online demo)}

And yes, I would recommend to specify the ordering explicitly, making use of WITH ORDINALITY.