How to delete JSON Object from JSON array in Postgres?

Question

I am new to Postgres. I want to delete the JSON object from the JSON array.

I have a table where I am using jsonb column in that I am storing JSON array as like below.

[
  {
    "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
  }
]

I want to remove JSON objects from an array that contains a null value in the name key.

after removing answer should be like this

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

anyone, please help me to write the SQL query,

I know how to get all the records from the table which contains the null value.

SELECT * 
  FROM table_name 
 WHERE jsonb_col_name @>CAST('[{"name": null}]' AS JSONB);

But I don't know how to make a delete query please help me with this. How Can I do it using a query?

Jim Jones · Accepted Answer · 2021-11-10 12:38:46Z

4

Unnest the array with jsonb_array_elements, exclude the null values with a FILTER 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

edited Nov 10, 2021 at 12:38

answered Nov 10, 2021 at 12:32

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Comments

Barbaros Özhan · Accepted Answer · 2021-11-10 13:16:53Z

3

You can filter out by j.value ->> 'name' IS NOT NULL after splitting the array into sub-objects by using JSONB_ARRAY_ELEMENTS and then apply JSONB_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

edited Nov 10, 2021 at 13:16

answered Nov 10, 2021 at 12:38

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

Ajax1234 · Accepted Answer · 2021-11-10 12:19:45Z

1

You can use json_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"}]

answered Nov 10, 2021 at 12:19

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Collectives™ on Stack Overflow

How to delete JSON Object from JSON array in Postgres?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related