json_array_elements with null values

Question

I'm using PostgreSQL 9.4.5, 64 bit on windows. I've got some irregular sized arrays. I want to use json_array_elements to expand the arrays similar to the following code

with outside as (select (json_array_elements('[[],[11],[21,22,23]]'::json)) aa, json_array_elements('[1,2,3]'::json)bb) 
select json_array_elements_text(aa), bb from outside

However, when i run this, i get

aa | bb
------- 
11 |  2
21 |  3
22 |  3
23 |  3

The empty array in column aa is dropped on the floor along with the the value of 1 in column bb

I would like to get

aa   | bb
---------- 
null |  1
11   |  2
21   |  3
22   |  3
23   |  3

Also, is this a bug in PostgreSQL?

Community · Accepted Answer · 2017-05-23 11:46:55Z

5

You are using the right functions, but the wrong JOIN. If you (possibly) don't have rows on one side of the JOIN & you want to keep the rows from the other side of the JOIN & use NULLs to "pad" rows, you'll need an OUTER JOIN:

with outside as (
    select json_array_elements('[[],[11],[21,22,23]]') aa,
           json_array_elements('[1,2,3]') bb
) 
select    a, bb
from      outside
left join json_array_elements_text(aa) a on true

Note: it may seem strange to see on true as the joining condition, but it is actually quite general, when you are using LATERAL joins (which is implicit when you use a set returning function (SRF) directly in the FROM clause).

http://rextester.com/KNW13145

Edit: your original query does not involve a JOIN directly, but worse: you use a SRF in the SELECT clause. This is almost like a CROSS JOIN, but actually it has its own rules. Don't use that unless you know exactly what you are doing and why you need that.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Apr 25, 2017 at 8:20

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

1 Comment

newman911 Over a year ago

thank you - good answer, and left join is cleaner then the case statement by klin

klin · Accepted Answer · 2017-04-24 21:45:47Z

3

This is not a bug. json_array_elements_text('[null]') returns null, json_array_elements_text('[]') does not return anything.

with outside as (
    select (
        json_array_elements('[[],[11],[21,22,23]]'::json)) aa, 
        json_array_elements('[1,2,3]'::json) bb
) 
select elem as aa, bb
from outside,
json_array_elements_text(case when aa::text = '[]' then '[null]'::json else aa end) elem;

 aa | bb 
----+----
    | 1
 11 | 2
 21 | 3
 22 | 3
 23 | 3
(5 rows)

answered Apr 24, 2017 at 21:45

klin

123k15 gold badges240 silver badges262 bronze badges

2 Comments

newman911 Over a year ago

i agree that json_array_elements_text works as you describe, why is that correct behavior?

klin Over a year ago

Because there is a difference between '[null]'::json and '[]'::json. The first array contains null, the second is empty. The function cannot return anything from an empty array.

newman911 · Accepted Answer · 2017-04-24 22:07:48Z

0

Working through my own problem, i've got one possible answer, but it seems like a mess

With initial as (select '[[],[11],[21,22,23]]'::json as a, '[1,2,3]'::json as b),
    Q1 as (select json_array_elements(a) as aa, json_array_elements(b) bb from initial),
    Q2 as (select ARRAY[aa->>0, aa->>1, aa->>2] as aaa, bb as bbb, ARRAY[0,1,2] as ccc from q1), 
    -- where the indicices are computed in a separate query by looping from 0 to json_array_length(...)
    Q3 as (select unnest(aaa) as aaaa, bbb as bbbb, unnest(ccc) as cccc from q2)

Select aaaa, bbbb from q3 where aaaa is not null or cccc = 0

answered Apr 24, 2017 at 22:07

newman911

3133 silver badges8 bronze badges

Collectives™ on Stack Overflow

json_array_elements with null values

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related