0

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?

2
  • Try dbfiddle. – wchiquito Sep 29 '19 at 8:52
  • Thank you @wchiquito. It worked. And now my query is 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; – Abdul Haye Oct 1 '19 at 12:18
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.