postgresql, select multiple json_array_elements works so werid

Question

I want use json_array_elements to expands json array. But it works so werid. Pls see below.

select json_array_elements('[1, 2]') as a, json_array_elements('[2, 3, 4]') as b;

a | b
---+---
1 | 2
2 | 3
1 | 4
2 | 2
1 | 3
2 | 4

(6 rows)

select json_array_elements('[1, 2]') as a, json_array_elements('[2, 3]') as b;

a | b
---+---
1 | 2
2 | 3

(2 rows)

It's seems when the length of the arrays are equal, something goes wrong. Can anyone tell me, why is like this.

My postgresql's version is 9.4.4. I installed 9.6.2 and run the test sql, nothing changed. — simo yang, Feb 13 '17 at 03:30
[SQL multiple UNNEST in single select list](http://stackoverflow.com/q/23003601/1995738) — klin, Feb 13 '17 at 03:47

Laurenz Albe · Accepted Answer · 2017-05-17T06:47:20.133

PostgreSQL repeats each list until both happen to be at the end simultaneously.

In other words, the length of the result list is the least common multiple of the length of the input lists.

This behaviour is indeed weird, and will be changed in PostgreSQL v10:

select json_array_elements('[1, 2]') as a, json_array_elements('[2, 3, 4]') as b;
 a | b
---+---
 1 | 2
 2 | 3
   | 4
(3 rows)

From the commit message:

While moving SRF evaluation to ProjectSet would allow to retain the old "least common multiple" behavior when multiple SRFs are present in one targetlist (i.e. continue returning rows until all SRFs are at the end of their input at the same time), we decided to instead only return rows till all SRFs are exhausted, returning NULL for already exhausted ones. We deemed the previous behavior to be too confusing, unexpected and actually not particularly useful.

Oh, that why. thanks very much. And when PostgreSQL add this change, will it in PostgreSQL 10? — simo yang, May 17 '17 at 04:09

postgresql, select multiple json_array_elements works so werid

1 Answers1