21 Dec 2016

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. Saul Uribe23 December 2016 at 23:02

    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. Unknown25 December 2016 at 16:14

    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. Jens C. Kruse20 March 2017 at 09:30

    nice

    ReplyDelete
  4. Unknown4 August 2017 at 15:18

    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. Neeraj Vimla Goyal12 September 2017 at 07:49

    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. Jonathan Levin12 September 2017 at 11:16

    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