Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

323
Vistas
json_array_elements with null values

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?

over 4 years ago · Santiago Trujillo
3 Respuestas
Responde la pregunta

0

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.

over 4 years ago · Santiago Trujillo Denunciar

0

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)    
over 4 years ago · Santiago Trujillo Denunciar

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
over 4 years ago · Santiago Trujillo Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda