Mysql JSON_REMOVE Array Key and Value (MariaDB)

Question

I have the following JSON in MariaDB/MySQL:

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

 user_pst_tb
------------------------------
pst_id | pst_liked_by
--------------------------
1      |[]
-------|----------------
.      |[{"uid": 9}]
-------|----------------
.      |[]
-------|----------------
29     |[]
-------|----------------
30     | [{"uid": 5}, {"uid": 6}, {"uid": 7}]

i want to use JSON_REMOVE or 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

UPDATE 1 (some GOOD NEWS) I have tried this

 UPDATE user_pst_tb SET `pst_liked_by` = 
      JSON_REMOVE(`pst_liked_by`,JSON_UNQUOTE(JSON_search(`pst_liked_by`, 
'one', '6'))) WHERE `pst_id` = 30;

Somehow working but it leaves some empty {} behind.

Example: [{"uid": 5}, {}, {"uid": 7}] any idea to remove the empty brackets will be great!!

Now you're learning why the JSON datatype is such a pain to use. Normalize your tables and things will be much easier. — Barmar
– Barmar, Commented Nov 24, 2022 at 5:20
Thanks for your comment, but so far the JSON approach for this purpose seems very feasible. Any help is highly apprecuated — ErickBest
– ErickBest, Commented Nov 24, 2022 at 5:36
maybe JSON_TABLE helps this JSON structure. but only supports mysql 8.0 or higher... — rodpold
– rodpold, Commented Nov 24, 2022 at 6:22
JSON_TABLE in MariaDB-10.6+ too, but I'm not sure it helps. JSON_REMOVE and predicates seems the right way, but I haven;t worked out the incantation. — danblack
– danblack, Commented Nov 24, 2022 at 7:57

WhatsThePoint · Accepted Answer · 2022-11-24 10:58:17Z

2

I was assisted by @ypercubeᵀᴹ

The final query that worked is:

UPDATE nz_psts_01
    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 = 29
 and JSON_SEARCH( `pst_liked_by`, 'one', '6', null, '$**.uid' ) is not null ;

Hope it can help someone

edited Nov 24, 2022 at 10:58

WhatsThePoint

3,63511 gold badges35 silver badges58 bronze badges

answered Nov 24, 2022 at 10:47

ErickBest

4,7205 gold badges33 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Mysql JSON_REMOVE Array Key and Value (MariaDB)

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