Tuesday, September 27, 2011

mysql_json - a MySQL UDF for parsing JSON

Somewhat related to (or rather not related to) やったーJavaScriptの動くMySQLできたよー - 愛と勇気と缶ビール, I have written a MySQL UDF that parses JSON strings.

The UDF introduces one function: json_get, that parses a JSON object or an array and returns one of the properties.

SELECT json_get('{"a":1}', 'a')       => 1
SELECT json_get('{"a":1}', 'b')       => NULL
SELECT json_get('[1,2,3]', 2)         => 3
SELECT json_get('{"a":[2]}', 'a', 0)  => 2

By using to the UDF it is possible to write queries accessing the properties of JSON objects stored in MySQL.

SELECT json_get(data,'title') FROM message WHERE id=1;
SELECT id,data FROM message WHERE json_get('data','from')='hidek';

Source code of the UDF can be found at github.com/kazuho/mysql_json. The installation process may vary, but I did the following for my ubuntu server.

% g++ -shared -fPIC -Wall -g mysql_json.cc -o mysql_json.so
% sudo cp mysql_json.so /usr/lib/mysql/plugin
% mysql -u root
mysql> create function json_get returns string soname 'mysql_json.so';

102 comments:

  1. Replies
    1. My friend Mentioned to me your blog, so I thought I'd read it for myself. Very interesting insights, will be back for more!
      buy tumblr followers
      https://www.youtube.com/watch?v=OsodbtYnlyY

      buy tumblr followers

      Delete
  2. I was getting following error:

    mysql_json.cc: In member function ‘bool {anonymous}::filtered_context::parse_array_stop({anonymous}::size_t)’:
    mysql_json.cc:181:30: error: ‘to_string’ is not a member of ‘std’
    _returning_buffered_str() = std::to_string(idx);

    till I have added -std=c++11 to the g++ command line

    ReplyDelete
    Replies
    1. workaround for old compilers:

      namespace std
      {
      template < typename T > std::string to_string( const T& n )
      {
      std::ostringstream stm ;
      stm << n ;
      return stm.str() ;
      }
      }

      Delete
  3. mysql_json.cc:33:25: fatal error: mysql/mysql.h: No such file or directory
    compilation terminated.

    I am facing problem in it kindly help

    ReplyDelete
  4. I gathered useful information on this point . Thank you posting relative information and its now becoming easier to complete this assignment
    mahjong | agario | minecraft|halloween | pacman| kizi | hulk | geometry dash | sniper games | super smash flash

    ReplyDelete
  5. This is jonesdruman This blog was simply great post byb you. I really appreciate your blog. Really awesome blog I would like to know more about this. Please give some ideas about your blog. Thanks…………………………………….
    Asia Cup T20 2016 Teams, Squad
    Asia Cup T20 2016 Timetable & Fixtures
    New Zealand Captain Name for T20 World Cup 2016

    ReplyDelete
  6. I would like to thank you for your nicely written post
    happy wheels , agario , happywheels , agar ,

    ReplyDelete
  7. good article and it is amazing status and regards
    whatsapp status and other status

    ReplyDelete
  8. This is very amazing to see such work over here. Slitherio | Agario | Happy Wheels | happy fathers day 2016 messages Carry on the discussions over night.

    ReplyDelete
  9. I enjoyed over read your blog post. Your blog have nice information,
    I got good ideas from this amazing blog.
    I am always searching like this type blog post. I hope I will see again..
    Signature:
    soi cau lo chinh xac 100
    | soi cau |soi cau mb | soi cau mien bac

    ReplyDelete
  10. JNTUK 4-2 Advanced Supply Results 2016 http://www.jntu--results.com/p/jntuk-btech-4-2-r10-r07-r05-advanced.html
    jntu results http://www.jntu--results.com/
    jntuk results http://www.jntu--results.com/
    jntuh results http://www.jntu--results.com/
    jntua results http://www.jntu--results.com/

    ReplyDelete
  11. JNTUK 4-2 Advanced Supply Results 2016 http://www.jntu--results.com/p/jntuk-btech-4-2-r10-r07-r05-advanced.html
    jntu results http://www.jntu--results.com/
    jntuk results http://www.jntu--results.com/
    jntuh results http://www.jntu--results.com/
    jntua results http://www.jntu--results.com/

    ReplyDelete

  12. This app is great it gives you those moments of relaxation and incredibly wonderful
    age of war 2 | tank trouble | gold miner | tank trouble 3
    Just wanted to tell you keep up the fantastic work!my weblog:
    age of war 3 |earn to die 3 | earn to die 2 | red ball
    Stick war 2 | Apple Shooter

    ReplyDelete
  13. This is a great article. It gave me a lot of useful information. thank you very much. Can you play more games at :
    swords and souls | strike force kitty 2 | swords and souls game | strikeforce kitty 2

    ReplyDelete
  14. I found a lot of information here to create this actually best for all newbie here. Thank you for this information.
    ninjago games | red ball | ninjago game | red ball 4

    ReplyDelete
  15. Thanks for the best blog.it was very useful for me.keep sharing such ideas in the future as well.this was actually what i was looking for,and i am glad to came here!
    kids games free | games games | shooting games | tank games | apple shooter | stick war 2 | unblocked games

    ReplyDelete
  16. this is one of the cult game now, a lot of people enjoy playing them . Also you can refer to the game :
    age of war | earn to die 5 | Tank trouble | happy wheels | earn to die 6
    The game controls are shown just under . Movement mechanisms primarily include acceleration and tilting controls.
    tank trouble unblocked | wings io | strike force heroes

    ReplyDelete
  17. for beginners like me need a lot of reading and searching for information on various blogs. and articles that you share a very nice and inspires me . cara menggugurkan kandungan

    ReplyDelete
  18. Latest Tips and Tricks @ http://freshtricks.in

    ReplyDelete
  19. Hi,

    getting this error on AWS Linux .

    g++ -shared -fPIC -Wall -g mysql_json.cc -o mysql_json.so
    mysql_json.cc:38:31: fatal error: picojson/picojson.h: No such file or directory
    #include "picojson/picojson.h"

    ReplyDelete
  20. It is a great website.. If you are games lover and love to play games word cookies game download is the most easiest way to get latest games hill climb racing 2 anytime you need to follow just few simple steps five nights at freddys 4 | hotmail login

    ReplyDelete
  21. This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years.
    شركة نقل اثاث بالقطيف
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالدمام
    شركة نقل عفش بالدمام
    شركة نقل اثاث بالجبيل
    شركة نقل العفش بالخبر
    Thanks for this great post, i find it very interesting and very well thought out and put together. I look forward to reading your work in the future
    شركة نقل اثاث بالخبر
    ارخص شركة نقل اثاث بالدمام
    شركة نقل الاثاث بالدمام
    شركة نقل عفش بالدمام
    شركات نقل العفش والاثاث بالدمام والخبر على وجه الخصوص تعددت كثيرا ولكن القليل من شركات نقل الاثاث داخل الدمام والخبر ما زال يقدم الخدمة المنوط بها للعميل فنحن فى شركة نقل اثاث بالدمام وبالخبر افضل شركات نقل اثاث بالدمام والخبر والقطيف والجبيل على مستوي المملكه العربية السعودية وخاصة منها المنطقة الشرقية ونحن ارخص شركة نقل اثاث بالدمام وبالخبر

    ReplyDelete
  22. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    شركة نقل اثاث بالقطيف
    نقل اثاث بالدمام رخيص
    Hey – great blog, just looking around some blogs, seems a really nice platform you are using. I’m currently using WordPress for a few of my blogs but looking to change one of them over to a platform similar to yours as a trial run. Anything in particular you would recommend about it?
    ارخص شركة نقل اثاث بالدمام
    شركة نقل عفش بالدمام
    شركة نقل اثاث بالخبر
    شركة نقل اثاث بالدمام

    ReplyDelete
  23. thank you so much for this very usefull information. It's just a very effectively written article. It will likely be useful to anyone who makes use of it
    I was very pleased to find this site.I wanted to thank you for this great read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you post
    شركة تنظيف بالخبر
    شركة تنظيف بالجبيل
    شركة تنظيف بالدمام
    شركة تنظيف بالقطيف

    ReplyDelete
  24. I really enjoyed reading this post, big fan. Keep up the good work andplease tell me when can you publish more articles or where can I read more on the subject?
    شركة تنظيف بالجبيل
    شركة تنظيف بالدمام
    شركة تنظيف بالخبر
    شركة تنظيف بالقطيف

    ReplyDelete
  25. All the best blogs that is very useful for keeping me share the ideas of the future as well this is really what I was looking for, and I am
    very happy to come here. Thank you very much
    animal jam codes | five nights at freddy's 4 | hotmail login

    ReplyDelete
  26. View your current location and surrounding hills on a map. downlodable torrent Save your designs to edit and update laterSHARE YOUR DESIGNS- Save your favorite designs to your camera roll.

    ReplyDelete
  27. You Should Also Check These Links, because it can help you a lot
    Lava Hound Deck
    Songsterr APK

    ReplyDelete
  28. هل تعاني من الكثير من الحشرات المزعجة فى منزلك, وتريد ان تتخلص منها بطريقة امنة تقدم لكم خدمة القضاء الحشرات بالدمام
    للقضاء التام على كل انواع الحشرات التي تسبب الكثير من الامراض, فسكان المملكة السعودية معروضون الى اصابة منازلهم دائما بالحشرات
    لان المناخ صحراوي بالمملكة ولذلك عملينا العزيز يجب عليك فورا التخلص من تلك الحشرات اذا ظهرت لكى لا تتكاثر بمنزلك وتسبب لك الكثير من الامراض الخطيرة على صحتك
    وعلى صحة اسرتك كثير من سكان المملكة السعودية تصاب منازلهم بالحشرات المزعجة والتي تسبب الكثير من المتاعب في الكثير من الأُسر
    , نحن في شركة مكافحة الحشرات بالدمام نقوم بتخليصك من كل الحشرات المزعجة الى الابد وعدم رجوعها ابدا
    [URL=https://colocleansepro4u.net/the-control-of-rats-in-qatif/]شركة مكافحة فئران بالقطيف[/URL]
    فنحن ذو خبرة لأعوام في مكافحة الحشرات ورش المبيدات نتخلص من كل الحشرات المزعجة وذلك عن طريق عمالة مدربة جيداً وذو خبرة ممتازة في مكافحة
    الحشرات عمالتنا يقومون بالتخلص من الحشرات باستخدام المبيدات اللازمة الأمنة .
    [URL=https://colocleansepro4u.net/an-insect-control-buraidah/]شركة مكافحة حشرات ببريدة[/URL]نقوم بالتخلص من كافة انواع الحشرات
    مثل الصراصير وحشرات النمل الابيض, وأيضا مكافحة بق الفراش, و مكافحة الخنافس و النمل الاسود و مكافحة حشرات العته ايضا مكافحة الفئران,
    و مكافحة الثعابين ومكافحة جميع انواع الحشرات الاخرى السامة والغير سامة .
    [URL=https://colocleansepro4u.net/qatif-insect-control/]شركة مكافحة حشرات بالقطيف[/URL]
    [URL=https://colocleansepro4u.net/buraidah-pesticide-spraying/]شركة رش مبيدات ببريدة[/URL]
    إطمن دائما معنا فلدينا عمالة ماهرة في مكافحة الحشرات فان كنت تعاني من الحشرات فلن تجد افضل من شركتنا
    [URL=https://colocleansepro4u.net/elkatef-home-clean/]شركة تنظيف منازل بالقطيف[/URL]
    العمالة لدينا مخصصة لمكافحة الحشرات حيث يقومون بالتخلص من كافة انواع الحشرات مع العلم ان كل حشرة لها مبيد حشري معين,
    تلك العمالة تتوصل ايضا الى المكان الذي تأتي منه تلك الحشرات فان كانت تلك الحشرات الموجودة بمنزلك صراصير فلابد ان يوجد مكان في منزلك يدخل من خلاله
    تلك الصراصير والتوصل لذلك المكان هو الحل لنصف المشكلة وبعد ذلك نتخلص من الحشرات بسهولة تامة الموجودة بالمنزل بسهولة
    لاننا ذات خبرة طويلة في مكافحة حشرات بالدمام, توجد المئات من شركات المكافحة للحشرات بالمملكة
    التي تقدم كل خدمات [URL=https://colocleansepro4u.net/eltaif-pesticide-spraying/]شركة رش مبيدات بالطائف[/URL]ومكافحة الحشرات
    ولكننا في شركتنا نتميز عن باقي الشركات بالعديد من الاشياء والتى تجعلنا من افضل الشركات واهمها واساسها الخبرة والسعر والمستوى والعمالة
    [URL=https://colocleansepro4u.net/al-qatif-transport/]شركة نقل عفش بالقطيف[/URL]
    [URL=https://colocleansepro4u.net/water-leak-detection-elkatef/]شركة كشف تسربات المياه بالقطيف[/URL]

    ReplyDelete
  29. You can Download Parallel Space App from our blog here and It is completely free for download. Share it with your friends too.

    ReplyDelete
  30. If you are waiting for your flight at the airport then this is a must-have app for you. downlodable warez It is now yours when you tap into the strategies she has put together for you.

    ReplyDelete
  31. Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write.
    Thanks for sharing !
    serato dj 1.7 crack mac
    mixed in key download free

    ReplyDelete
  32. The blog or and best that is extremely useful to keep I can share the ideas of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    animal jam | five nights at freddy's | hotmail login

    ReplyDelete
  33. Your Mobile Apps Inc, has developed the outstanding ebook reader for iPhone. downlodable tv shows Nr du tegner bogstaver, er der nu mulighed for at vlge, om du vil have hjlp til skriveretningen eller ej.

    ReplyDelete
  34. The stage is being set for a future filled with unending rage and violence. downlodable pdf Build up a "streak" by hitting the schedule you define and then try to make the streak as long as possible.

    ReplyDelete
  35. This is a very good article material and it is very useful for us all. thank you . cara menggugurkan kandungan

    ReplyDelete
  36. Note: To delete a Tree from your Sanctuary, please hold down on the tree as you would an App on your iPhone. Click To Discover More! Do you know that you can discover your past life through the powers of Numerology, Astrology, Chinese Zodiac and Feng Shui?

    ReplyDelete
  37. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
    mcdonaldsgutscheine | Startlr.com

    ReplyDelete
  38. ONLINE FEATURES (requires free MedHelp registration): Backup your data online. My page. You don't need to know any ancient Greek, but Greek happens to be the language God chose to record His (new) covenant with mankind.

    ReplyDelete
  39. Wonderful piece of information.
    Buy tumblr followers https://www.facebook.com/Buytumblrfollowerscheap/
    #Miami #EastCoast #EasternUnitedStates #NorthAtlanticOcean #EasternSeaboard #AtlanticCoast #AtlanticSeaboard #coastalstates #shoreline #AtlanticOcean #Maine #NewHampshire #Massachusetts #RhodeIsland #Connecticut #NewYork #NewJersey #Delaware #Maryland #Virginia #NorthCarolina #SouthCarolina #Georgia #Florida

    ReplyDelete
  40. We've given the app a refresh for the new tour - we hope you like it. mantra pushpam telugu audio More than 2 million people worldwide have used this app to lose weight.

    ReplyDelete
  41. Thank you both for sharing Holy Mass with us each day FNAF Sister Location Juegos Friv Friv 2018 to make yourselves available Friv Twizl Jeux De Twizl Juegos De FNAF Sister Location when we were called out late at night and any other time and for your support and encouragement in every respect. Juegos Friv 2021 Juegos Twizl Juegos Yepi 2017 Juegos De Twizl

    ReplyDelete
  42. nice blog, thank you
    Danke, dafur dass Ihr jeden Tag mit uns die Hl. Messe gefeiert habt, fur all die Reparaturen im ganzen Haus Friv 10 Friv 2018 Friv 2019 fur Euere Bereitschaft Friv 10 Friv 20 Friv 2 immer zur Verfugung zu stehen Geometry Dash Twizl Twizy Twizl Danke, dass Sie Ihr Muhen um den Aufbau des Leibes Christi mit uns teilten.

    ReplyDelete
  43. A todos que partilham e trabalham sob estas mesmas convicções e princípios Friv Friv 360 Friv4school 2020 Senhor Deputado Cashman, agradeço-lhe a informação. Jeux De Friv 2018 Juegos De Roblox Juegos De Zoxy Mais uma vez, obrigada ao Parlamento por comungar da visão que informa a nova política dos consumidores Juegos Kizi 2017 Juegos Yepi 2017 Twizl 3 Zoxy 2 assente no mercado - a visão de um mercado de consumidores informados e capacitados que procuram e usufruem, com confiança,

    ReplyDelete
  44. Statistics comprehensive updated information pre, during and post match. http://andromarkets.ru Even better, Chef Tips is organized by food categories making it easy for you to find a recipe you want.

    ReplyDelete
  45. Wir danken Ihnen Friv4school 2018 Gry Friv 2 Gry Friv 5 dass Sie diese Hoffnung mit uns teilen und diesen Schritt auf dem Pilgerweg des Vertrauens Gry Friv Juegos Friv 100 Juegos Friv 1000 mit uns gegangen sind. Juegos Friv 5 Juegos De Friv 2 Juegos Friv 250 Juegos Yepi wir danken Ihnen für das Interesse an unseren Produkten und hoffe

    ReplyDelete
  46. Gracias por compartir sus ideas con nosotros. Friv Games Gry Friv gracias por la detallada información. Juegos Friv Gry Friv Friv Gracias por vuestras reacciones a las transmisiones que os han hecho. Friv Games Juegos Friv Acogemos con agrado los comentarios de los lectores.

    ReplyDelete
  47. I haven't found anything wrong yet, so far so good. This is what you want Beyton scribe eliminates these issues with an intuitive and easily accessible user interface.

    ReplyDelete
  48. Really impressive post. I read it whole and going to share it with my social circules. I enjoyed your article and planning to rewrite it on my own blog.
    learn more | learn more | learn more | learn more | learn more | learn more

    ReplyDelete

  49. This blog is a great source of information which is very useful for me.

    Jual Obat Aborsi Pekanbaru

    Jual Obat Aborsi Bekasi

    Jual Obat Aborsi malang

    Jual Obat Aborsi

    Obat Aborsi semarang

    Obat Aborsi yogyakarta

    Obat Aborsi solo

    Obat Aborsi

    -Can be very slow but shows all backlinks along with their PR, Anchor and if it's a Nofollow

    ReplyDelete
  50. This blog is a great source of information which is very useful for me.

    Jual Obat Aborsi Pekanbaru

    Jual Obat Aborsi malang

    Obat Aborsi semarang

    -Can be very slow but shows all backlinks along with their PR, Anchor and if it's a Nofollow

    ReplyDelete
  51. Avec FidelPass, une notification sera souvent accompagne dun coupon de promotion ajout directement dans votre compte fidlit. http://downloadtopthings.top GrooveMaker gives you a completely new way of making music with loops.

    ReplyDelete