0

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 ) i2WHERE i2.id = i.id;

But ERROR comes as below :-

ERROR: function json_array_elements(text) does not exist

askedMar 9, 2021 at 10:28
Dot Net Dev 19's user avatar
2
  • Your column is no defined asjsonb (or at leastjson) so you need to cast it, e.g.json_array_elements(jsondata::json) - the best solution is to change its data type fromtext tojsonb usingalter table (and then use the jsonb_xxxx functions)CommentedMar 9, 2021 at 10:34
  • 2
    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.CommentedMar 9, 2021 at 10:35

1 Answer1

1

demo:db<>fiddle

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

SELECT    id,    json_agg(elems)                               -- 3FROM mytable,    json_array_elements(jsondata::json) as elems  -- 1WHERE elems ->> 'id' <> 'app2'                    -- 2GROUP BY id
  1. Cast text into JSON; Extract the JSON array into a record per element
  2. Filter the required objects
  3. Reaggregate remaining elements into a new JSON array
answeredMar 9, 2021 at 10:38
S-Man's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.