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';

64 comments:

  1. 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
  2. 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
  3. 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
  4. 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
  5. I would like to thank you for your nicely written post
    happy wheels , agario , happywheels , agar ,

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

    ReplyDelete
  7. 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
  8. 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
  9. 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
  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. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. Latest Tips and Tricks @ http://freshtricks.in

    ReplyDelete
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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