How can I remove null values from a postgres jsonb array?

Question

Given a jsonb field with a value of

{
  values: [null, 'test', { key: 'value' }]
}

is it possible to return an output of

{
  values: [ 'test', { key: 'value' }]
}

Ive looked at the docs and have found some functions like jsonb_strip_nulls() which this only works for keys with null values and array_remove() which I cannot get to work with jsonb.

I would use

UPDATE table 
  SET data = jsonb_set(data, '{values}'::text[], jsonb_agg(elem), false)
FROM data, 
     jsonb_array_elements(data-> 'values') WITH ORDINALITY AS t(elem, nr)
WHERE jsonb_typeof(elem) <> 'null'
GROUP BY (data.id)

and this would work correctly for mose cases, but I need it to work with the case:

{
  values: [null, null, null]
}

the above query does not return

{
  values: []
}

in this case.

Any help is appriciated!! Thanks...

Alexey Orlov · Accepted Answer · 2021-04-06 11:11:36Z

8

For PostgreSQL 12:

select jsonb_path_query_array('{"values": [null, "test", { "key": "value" }]}', '$.values[*] ? (@ != null)')

answered Apr 6, 2021 at 11:11

Alexey Orlov

1912 silver badges2 bronze badges

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

Comments

Kristo Mägi · Accepted Answer · 2017-07-11 17:28:05Z

4

With using coalesce you could achieve it like that:

WITH    test( data ) AS (
            VALUES
                ( $${"values": [null, "test", { "key": "value" }]}$$::jsonb ),
                ( $${"values": [null, null, null]}$$::jsonb )
        )
SELECT  jsonb_set(
            d.data,
            '{values}',
            coalesce(
                jsonb_agg(t.elem) FILTER ( WHERE NOT (t.elem = 'null') ),
                $$[]$$::jsonb
            ),
            FALSE
        )
FROM    test d,
        jsonb_array_elements( d.data-> 'values') WITH ORDINALITY AS t(elem)
GROUP BY d.data;


               jsonb_set
----------------------------------------
 {"values": ["test", {"key": "value"}]}
 {"values": []}
(2 rows)

NOTE: I used FILTER instead of WHERE check for null, but with coalesce usage your query should work also.

answered Jul 11, 2017 at 17:28

Kristo Mägi

1,70413 silver badges16 bronze badges

2 Comments

Eder F. Freitas Over a year ago

I know it is a old post, but for those like me still need a solution for this problem, you can use json_strip_nulls available on Postgres 9.5

Johannes H. Over a year ago

@EderF.Freitas No, you cannot. Json_strip_nulls was already menationed by the OP, and it only works for keys in objects, not for arrays.

Flo · Accepted Answer · 2025-03-13 07:27:09Z

4

This use to be a pain for me as well, so I submitted a patch upstream commited @ 4603903d2 and will be available in PG18.

json{b}_strip_nulls will now accept an additional flag strip_in_arrays to remove null array elements as well.

So you'll be able to do:

 select jsonb_strip_nulls('
{
    "a": 1,
    "b": null,
    "c": [ 5, 6, null, null, 9 ],
    "d": {
        "e": null,
        "f": [ null, null, { "x": null } ]
    }
}
', true );
             jsonb_strip_nulls
--------------------------------------------
 {"a": 1, "c": [5, 6, 9], "d": {"f": [{}]}}
(1 row)

answered Mar 13 at 7:27

Flo

1,4371 gold badge15 silver badges27 bronze badges

Comments

o.d · Accepted Answer · 2025-09-28 13:19:24Z

0

Before PG 18 :

SELECT jsonb_build_array(
    VARIADIC array(SELECT v FROM jsonb_array_elements('[1, 2, null, 3]'::jsonb) v WHERE v != 'null'::jsonb)
);

-- -> [1, 2, 3]

answered Sep 28 at 13:19

o.d

6,8341 gold badge15 silver badges3 bronze badges

Collectives™ on Stack Overflow

How can I remove null values from a postgres jsonb array?

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related