postgres remove json object from json array

Question

I am using postgres 12.X, I have the following logs table :-

logs
id bigint , jsondata text

Following are values in json

[
  {
    "loginfo": "somelog1",
    "id": "app1"
  },
{
    "loginfo": "somelog2",
    "id": "app2"
  }
  ]

I need remove all json objects from this text column where id == "app2"

tried the below as per answer here :- https://dba.stackexchange.com/questions/84472/how-to-remove-object-from-json-array

UPDATE logs i SET    jsondata = i2.jsondata FROM  (SELECT id, array_to_json(array_agg(elem)) AS jsondata
  FROM   logs cand
       , json_array_elements(cand.jsondata) elem
  WHERE  cand.jsondata @> '{[{"id":"app2"}]}'::jsonb
  AND    elem->>'id' <> 'app2'
  GROUP  BY 1 ) i2
WHERE i2.id = i.id;

But ERROR comes as below :-

ERROR: function json_array_elements(text) does not exist

Your column is no defined as jsonb (or at least json) so you need to cast it, e.g. json_array_elements(jsondata::json) - the best solution is to change its data type from text to jsonb using alter table (and then use the jsonb_xxxx functions) — user330315
– user330315, Commented Mar 9, 2021 at 10:34
Don't abuse JSON like this. Store these data in a separate table without JSON columns and have a foreign key relationship between the tables. Then the exercise becomes trivial. — Laurenz Albe
– Laurenz Albe, Commented Mar 9, 2021 at 10:35

S-Man · Accepted Answer · 2021-03-09 10:38:23Z

1

demo:db<>fiddle

Why do you store JSON objects in text columns? In that case you need to cast it before using it as JSON:

SELECT
    id,
    json_agg(elems)                               -- 3
FROM mytable,
    json_array_elements(jsondata::json) as elems  -- 1
WHERE elems ->> 'id' <> 'app2'                    -- 2
GROUP BY id

Cast text into JSON; Extract the JSON array into a record per element
Filter the required objects
Reaggregate remaining elements into a new JSON array

answered Mar 9, 2021 at 10:38

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

postgres remove json object from json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related