Friday, July 6, 2018

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!


24 comments:

  1. Your very useful ideas did a great job of inspiring me. I was likely amazed at every detail you had posted. Best Assignment help

    ReplyDelete
  2. Your blogs are amazing and I am glad to read them. Thanks for sharing the tips and samples of our assignments. They are useful in knowing the key points that can increase the value of an assignment. And a special thanks to the My Assignment Help Australia for helping the students 24/7. You can email us at Info@Myassignmenthelpau.Com or Phone Number: +61-2-8005-8227

    ReplyDelete
  3. Students Assignment Help provides the best do my assignment services to the students. We are having the expert writers in the industry and also provide the excellent quality assignment writing services to the students from all over the world.

    ReplyDelete

  4. A high-standard post with all imperative information about assignment help UK services. Looking forward to avail the premium services.

    ReplyDelete
  5. The above information you shared give me a new look at this topic. I am working as a full-time academic writer in myassignmentHelpsg providing my assignment Help Singapore services to college students.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Assignment experts use impressive language to make the assignment rich in quality. They proofread and edit the write-up to guarantee that there are no silly mistakes in the assignment.

    ReplyDelete
  8. Information you shared is nice. It might help me somewhere in my life. I am a blogger and a Matlab programmer. I keep on searching information. I also provide Matlab Assignment Help to students looking for Matlab Experts. Thanks for sharing..!

    ReplyDelete
  9. I am glad to read your blog. By the way, you can use for free my Resume Writing Tips . It for sure a good chance for everyone.

    ReplyDelete
  10. Most of the
    Online assignment help seekers think that online assignment writing is an expensive affair. Our AllAssignmentHelp service experts offer variety of online services and Support for your Assignment, with the highly-qualified and certified experts in the team that is continue working 24x7.

    ReplyDelete
  11. I work as a writer on 5starswriting.com.The sites provides tips for Research and Lab Reports.They will help you to improve your writing skills and become a better writer. Visit us and make sure of that.

    ReplyDelete

  12. Being an academic writer from past 5 years providing assignment help to college and university students also associated with Myassignmenthelp platform. I am dedicated in providing best online academic writing services to the college students at the affordable rates.

    ReplyDelete
  13. A lot of valuable information can be derived from the post. Certainly, this compelling post will encourage readers to choose Assignment Help Australia services. You can email us at info@firstassignmenthelp.com.

    ReplyDelete
  14. Take help from the Creative Essay Writing services in order to write attractive and exciting essays for your assigned tasks. Contact the experts of Assignment Help 4 Me for the same.

    ReplyDelete
  15. Finding online essay writing help in Canada? Then StudentsAssignmentHelp.com is the best option for the students. Our writers are working to give the outstanding Essay Help services to all the clients and ensure they do not lose their valuable grades.

    ReplyDelete
  16. Programming Assignment help provided by allassignmenthelp takes care of these fundamentals and most of our tutors are efficient with every programming assignment.

    ReplyDelete
  17. Looking to free up your time? Consider the essays.com essay writing service! Our professional writers provide assistance and cheap essay writing that does not skimp on quality.

    ReplyDelete
  18. Hey,
    I was searching about some article on google and came across your website.
    I must say the content on this website is just amazing.
    I would love if you can write an article on this topic Ytmp3 – Best and Free YouTube to Mp3 Converter | Is It Safe?
    Anyways keep up your good work.
    Thanks.

    ReplyDelete
  19. Thank you for going above and beyond for all of us here on this blog and happy to share about dogpile web search that how it can harm your system.

    ReplyDelete
  20. Thanks for sharing this valuable information. I also have a reference for iPhone app development company which provides services like iPhone application development for various categories such as education, finance, health & fitness, baking etc.

    ReplyDelete
  21. Our assignment help experts could address students' academic topics quite well. Thus, they can opt for our service if they have difficulty in writing the academic task.
    assignment help uk

    ReplyDelete
  22. ABC Assignment Help provides professional Perdisco assignment help in various subjects for students studying in colleges and Universities across Australia, UK, USA, New Zealand and Canada. We deliver quality solutions for assignments in over 100 subjects. Our tutors focus on providing step-by-step solution to every assignment problem. As a trusted brand among students, we strive to provide 100% unique solutions at an affordable price.

    ReplyDelete
  23. Buy coursework help online, and you will never have any trouble! You do not need any special skills or knowledge to have your coursework done by a professional writer. Just provide the requirements for your order, and we will do the rest.

    ReplyDelete