1

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;
Yuri Astrakhan
  • 8,808
  • 6
  • 63
  • 97

1 Answers1

2

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.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Bergi, thx for the quick reply! Could you explain what does `els` mean in your query? Thx! – Yuri Astrakhan Dec 18 '22 at 02:30
  • 1
    It's the alias of the relation in the `FROM` clause, short for "elements". If you omit the alias entirely, you'd refer to `value` and `ordinality` (instead of `elem` and `o`). – Bergi Dec 18 '22 at 02:57