Combine JSONB array of values by consecutive pairs

Question

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;

Bergi · Accepted Answer · 2022-12-17 22:00:36Z

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.

answered Dec 17, 2022 at 22:00

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yuri Astrakhan Over a year ago

Bergi, thx for the quick reply! Could you explain what does els mean in your query? Thx!

Bergi Over a year ago

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).

Collectives™ on Stack Overflow

Combine JSONB array of values by consecutive pairs

Partial solution

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Partial solution

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related