Postgres - Remove NULL values from a json array

Question

I'm building a JSON ARRAY from a table which has a JSON column and non-JSON columns.

Here is my sample data and query:

create table check_details(SHORT_NAME VARCHAR, UNIQUE_NO JSON,STATUS VARCHAR);

insert into check_details values('Anu','{"ID":"1e699-76af2"}','REJECTED');

select json_agg(json_strip_nulls(
json_build_object('Name',SHORT_NAME,
'IDS',
jsonb_build_array(case SIGN(position('ACCEPTED' in STATUS) ) when 1 then UNIQUE_NO::jsonb->>'ID' else json_typeof(NULL::json) end) 
)))
from check_details;

I am getting this result:

[{"Name":"Anu","IDS":[null]}]

But I do not want to get "IDS":[null] part in my result when the value of the key IDS is NULL. How can I achieve this result:

[{"Name":"Anu"}]

When IDS has a valid value, it has to be an array. Hence using jsonb_build_array.

what is your postgres version

Akhilesh Mishra
– Akhilesh Mishra

2020-05-20 17:54:57 +00:00
Commented May 20, 2020 at 17:54 — Akhilesh Mishra
– Akhilesh Mishra, Commented May 20, 2020 at 17:54
I'm using version11.5

AnuC
– AnuC

2020-05-20 18:13:21 +00:00
Commented May 20, 2020 at 18:13 — AnuC
– AnuC, Commented May 20, 2020 at 18:13

Thom Brown · Accepted Answer · 2020-05-21 05:25:19Z

1

This is because you are placing the result of your CASE statement in a JSON array, so it's a non-empty array containing a JSON null rather than null JSON value.

So you would need to stop that being an array if you want the NULL to be stripped:

SELECT
  json_strip_nulls(
    json_build_object(
      'Name',
      short_name,
      'IDS',
      CASE SIGN(position('ACCEPTED' IN status) )
        WHEN 1 THEN (unique_no::jsonb->>'ID')::text
        ELSE NULL
      END
    )
  )
FROM
  check_details;

 json_strip_nulls 
------------------
 {"Name":"Anu"}
(1 row)

Note that json_strip_nulls() doesn't remove null values from JSON arrays.

Edit: But as you require non-null values to show as an array, move the jsonb_build_array() function into the case statement

SELECT
  json_strip_nulls(
    json_build_object(
      'Name',
      short_name,
      'IDS',
      CASE SIGN(position('ACCEPTED' IN status) )
        WHEN 1 THEN jsonb_build_array((unique_no::jsonb->>'ID')::text)
        ELSE NULL
      END
    )
  )
FROM
  check_details;

edited May 21, 2020 at 5:25

answered May 21, 2020 at 3:25

Thom Brown

2,0698 silver badges14 bronze badges

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

2 Comments

AnuC Over a year ago

It has to be an array when it has a valid value because that is how the downstream system expects it.

Thom Brown Over a year ago

Understood. I've updated my answer with a way to put non-null values into an array, otherwise it isn't output.

Collectives™ on Stack Overflow

Postgres - Remove NULL values from a json array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related