Unnest the array withjsonb_array_elements, exclude thenull values with aFILTER and aggregate them again e.g.

SELECT   jsonb_agg(j) FILTER (WHERE j->>'name' IS NOT NULL)FROM table_name t, jsonb_array_elements(jsonb_col) j GROUP BY t.jsonb_col;

Demo:db<>fiddle

You can filter out byj.value ->> 'name' IS NOT NULL after splitting the array into sub-objects by usingJSONB_ARRAY_ELEMENTS and then applyJSONB_AGG in order to convert it back to an array such as

SELECT JSONB_PRETTY(                    JSONB_AGG(j)                   )  FROM t,        JSONB_ARRAY_ELEMENTS(json_data) AS j WHERE j.value ->> 'name' IS NOT NULL GROUP BY json_data

If needed to update the existing data, then you might consider using

WITH tt AS(SELECT JSONB_AGG(j) AS new_val,       json_data              FROM t,        JSONB_ARRAY_ELEMENTS(json_data) AS j WHERE j.value ->> 'name' IS NOT NULL GROUP BY json_data)UPDATE t   SET json_data = new_val::JSONB  FROM tt WHERE t.json_data = tt.json_data

Demo

You can usejson_array_elements:

select json_agg(i.value) from json_array_elements('[{"id": "c75e7a-001e-4d64-9613-62f666d42103", "name": "Product1"}, {"id": "c75e7a-001e-4d64-9613-62f666d42103", "name": null}, {"id": "c75e7a-001e-4d64-9613-62f666d42103", "name": "Product2"}, {"id": "c75e7a-001e-4d64-9613-62f666d42103", "name": null}]') v where (v.value -> 'name')::text != 'null'

Output:

[{"id": "c75e7a-001e-4d64-9613-62f666d42103", "name": "Product1"}, {"id": "c75e7a-001e-4d64-9613-62f666d42103", "name": "Product2"}]

• sql
• postgresql
• jsonb