PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Remove values from array attribute of JSON column

I have a json (not jsonb) column in a PostgreSQL (version 13.12) table from which I want to remove specific values of an array attribute.

For example, in the following table (db-fiddle) I want to remove all occurrences of "D" from the actions attributes.

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  data JSON
);
INSERT INTO test VALUES
  ('{"name": "Alice", "actions": ["B", "C", "D"]}'),
  ('{"name": "Charles", "actions": ["C", "D", "E"]}');
SELECT * FROM test;

How can I do this with a query?

Tags:

3

Answers


  1. 0 
    select json_build_object('name', name, 'actions', json_agg(action)) as data
from (select data ->> 'name' as name, t.action
      from test,
           lateral json_array_elements_text(data -> 'actions') t(action)
      where action <> 'D') tmp
group by name;

    Output:

    data
    {"name" : "Alice", "actions" : ["B", "C"]}
    {"name" : "Charles", "actions" : ["C", "E"]}
    Login or Signup to reply.
  2. 0

    This query should do the trick, however, use a jsonb column if it’s feasible and get rid of all type castings in the query

    UPDATE test
SET data = CAST(jsonb_set(data::jsonb,'{actions}',(SELECT jsonb_agg(element) 
         FROM jsonb_array_elements(data::jsonb->'actions') AS element
         WHERE element != '"D"')) AS json
)
WHERE data::jsonb->'actions' @> '"D"';
    Login or Signup to reply.
  3. 0

    Just select:

    SELECT jsonb_set(data::jsonb, '{actions}', (data -> 'actions')::jsonb  - 'D')::json AS data
FROM   test;

    Update:

    UPDATE test
SET    data = jsonb_set(data::jsonb, '{actions}', (data->'actions')::jsonb  - 'D')::json;

    View on DB Fiddle

    Core feature is to use the jsonb operator -:

    jsonb - text → jsonb
    Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.

    Using jsonb would make things a bit easier, no casting back and forth.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search