CODEWITHSUNDEEP
jsonpostgresqlselect
simo yang
simo yang

Reputation: 23

postgresql, select multiple json_array_elements works so werid

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.

Upvotes: 1

Views: 1108

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247410

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.

Upvotes: 1

Related Questions