Comments - JSON_TABLE

4 months ago ebest bestism

I have the following JSON in MariaDB/MySQL:

[{"uid": 5}, {"uid": 6}, {"uid": 7}]

TABLE> user_pst_tb


pst_id | pst_liked_by (This is the JSON Column)


30 | [{"uid": 5}, {"uid": 6}, {"uid": 7}]

i want to use any method to remove {"uid": 6} alone on pst_id = 30, but i cannot find how to formulate the path. I thought of this:

UPDATE user_pst_tb SET `pst_liked_by` = JSON_REMOVE( `pst_liked_by`, JSON_UNQUOTE( REPLACE( JSON_SEARCH( `pst_liked_by`, 'one', '6', null, '$.uid' ) , '.uid' , '' ) ) ) WHERE pst_id = 30;

for some reason the MariaDB and MySQL docs does not have such examples. Any help is appreciated.

I have also tried:

UPDATE user_pst_tb SET `pst_liked_by`= JSON_REMOVE(`pst_liked_by`, JSON_UNQUOTE( JSON_SEARCH(`pst_liked_by`, 'one','{"uid": 6}') )) WHERE `pst_id` = 30;

The second query clears all the JSON data sadly

Please HELP

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.