CODEWITHSUNDEEP
postgresqljsonbpostgresql-9.5
Dalaigh88
Dalaigh88

Reputation: 427

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

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...

Upvotes: 4

Views: 8071

Answers (2)

Alexey Orlov
Alexey Orlov

Reputation: 181

For PostgreSQL 12:

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

Upvotes: 7

Kristo M&#228;gi
Kristo M&#228;gi

Reputation: 1684

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.

Upvotes: 4

Related Questions