Mysql – remove Json object from Json Array in Mysql 8. Column data type is Json

MySQLmysql-5.7

I'm using Mysql 8.
I want to remove the specific object from JSON Array which has value like field_1568988989723 in Mysql JSON column

Here is my JSON

[{"section": {"name": "Basic Info", "order": 0, "fields": [{"name": "field_1568988989723", "order": 0}, {"name": "field_1568989125942", "order": 1}]}}]

I've used this way

UPDATE apps_object
    SET detail_view = JSON_REMOVE( 
        detail_view, REPLACE( 
            JSON_SEARCH( detail_view, 'all', 'field_1568992047479', null, '$**.fields' )
            , '"'
            , ''
        ) 
      ) where id = 34;

But it just removes

"value":"field_1568988989723"

but not he whole object.
Is there any way to remove object in MySql not in Programing code?

Best Answer

I'm thankful to @wchiquito for solving my problem

[{"section": {"name": "Basic Info", "order": 0, "fields": [{"name": "field_1568988989723", "order": 0}, {"name": "field_1568989125942", "order": 1}]}}]

This is the query which solved my problem

UPDATE apps_object SET detail_view = IFNULL( JSON_REMOVE( detail_view, JSON_UNQUOTE( REPLACE( JSON_SEARCH(detail_view, 'all', 'field_1568988989723', NULL, '$**.fields'), '.name', '' ) ) ), detail_view ) where id = 34;

With the help of dbfiddle

Related Question