JSON and MySQL Stored Procedures

You probably heard that MySQL 5.7 supports JSON.
But did you know that you can also use JSON with MySQL Stored Procedures - making them very flexible?

Less talk, more code:
(Data was used from the UK Land Registry that I worked on)


8 comments:

  1. This method will be useful to work with node.js and the native mysql driver, just stringify the object to json, I will try

    ReplyDelete
  2. Using the JSON datatype for stored procedure params effectively gives us SPs with optional params. This takes stored procedures to a new level by making them a lot more flexible.

    ReplyDelete
  3. Thanks for sharing this. It's going to make building Stored Procedures with an unknown set of parameters much easier to develop AND maintain.

    ReplyDelete
  4. I'm getting a s yntax error when I use:
    CREATE PROCEDURE procedure_name(query JSON)

    It says that the JSON identifier is unexpected.

    ReplyDelete
  5. dear jonathan , I am very much new to mysql and json and encountering a prob .. requesting you please bear with me ..

    actually the thing is ... I have to read a table and stored in JSON array inside the stored procedure in mysql ( how we can achieve this) .. the intention behind this .. i have to generate a JSON file . please help me

    ReplyDelete
  6. There are some MySQL JSON functions in 5.7 to read JSON arrays, but bare in mind that they are limited and your best course of action is to read those using a programming language.
    I am however, happy to help if you have a specific issue with some details.

    ReplyDelete