Finding Values with JSON_CONTAINS

There was an interesting but hard to read post on StackOverflow about how 'insert select delete' data from a MySQL JSON data type column.  The first line of the writer's problem is a little confusing '

In order to record user mac_address and count mac_address to restrict user login's pc or notebook to control user available max for example (it's work)' but the examples reveled more about what was desired.

The idea was to track MAC address used by various users and the author of the question was wondering how to up data a JSON Array of values with JSON_INSERT.  INSERT is for inserting and the better choice would be JSON_ARRAY_APPEND or JSON_ARRAY_INSERT.   

But what caught my eye was the second question: Select sql command for json column ? could be example? If I want to check whether mac value exists 'c84wr8492eda' 

Well, here comes a shameless plug for my Book MySQL and JSON - A Practical Programming Guide  as it details how to do this sort of thing.  What is desired is a certain value (c84wr8492eda) and we can find that easily enough.  We know the key that needs to be searched (mac) and the desired MAC address. 

MySQL> select 
json_contains(auth_list,json_quote('c84wr8492eda'),'$.mac') 
from users;
+-------------------------------------------------------------+
| json_contains(auth_list,json_quote('c84wr8492eda'),'$.mac') |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+

A better code snipped would be SELECT id FROM users WHERE JSON_CONTAINS(auth_list,JSON_QUOTE('c84wr8492eda'),'$,mac') = 1; as you will probably be acting on the  'id' field with the matching MAC address.   

You can find answers to problems like this in my hands book available from Amazon.com and other book sellers.

The third question 'Delete sql command for json column ? Could be example? if I want to delete a item where mac value is 'c84wr8492eda'' was also unclear.  Delete the entire record or delete the MAC address from the JSON column?   Ah, the joys of StackOverflow.

DELETE FROM users WHERE JSON_CONTAINS(auth_list,JSON_QUOTE('c84wr8492eda'),'$,mac') = 1;  would remove the entire row.  But what about pruning the one item out of the array?  Well there is not a JSON_ARRAY_REMOVE_ONE_ITEM function.    I would want to get the values for auth_list into a string, removing the desired MAC address, and then using JSON_SET to rewrite the column.  But if you have other options, please let me know!