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

47 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