How to delete mySQL JSON column element by id, instead of by index num

user_1258530 0
Viewed 69

Provided a datasource:

https://raw.githubusercontent.com/usnistgov/oscal-content/master/examples/ssp/json/ssp-example.json

I have added this to a JSON column called json_data in a table called ssp_models, with a record uuid of 66c2a1c8-5830-48bd-8fdd-55a1c3a52888.

screenshot of how the record looks in myPHPAdmin

I am able to GET records, INSERT records, and REMOVE records just fine from the "parties" node with that dataset.

However, it's the JSON_REMOVE part that's not working quite as I'd like. Presently I can json_remove an entry via indexnum via:

UPDATE ssp_models 
   SET json_data = JSON_REMOVE(json_data, '$.\"system-security-plan\".metadata.parties[0]')

So that deletes entry [0] from 'parties' like you'd expect.

But what I really want is to be able to delete a record by uuid. As you can see in the 'parties' section of data, uuid is an element in each entry:

parties: [

{

uuid: "3b2a5599-cc37-403f-ae36-5708fa804b27",

type: "organization",

name: "Enterprise Asset Owners"

},

{

uuid: "833ac398-5c9a-4e6b-acba-2a9c11399da0",

type: "organization",

name: "Enterprise Asset Administrators"

}....

So how can I somehow combine 'WHERE uuid = "3b2a5599-cc37-403f-ae36-5708fa804b27"' for removal into such a sql query when it's part of the JSON Document Store syntax? (In that case, I'd expect it to remove the "Enterprise Asset Owners" entry along with it's type and uuid. It's like we need to add a search-function as part of the deletion, but the examples I've been reading don't provide that as an option within json_remove: https://database.guide/json_remove-remove-data-from-a-json-document-in-mysql/

sql-update mysql sql json
1 Answers

JSON_SEARCH() function might be used to find the element's index with the uuid value to be deleted such as

UPDATE ssp_models 
   SET json_data = JSON_REMOVE(json_data, 
                       REPLACE(
                        JSON_UNQUOTE(
                            JSON_SEARCH(json_data, 
                                        'one', 
                                        '3b2a5599-cc37-403f-ae36-5708fa804b27')
                            ),'.uuid',
                       ''))

Demo

user_58413060
Related