How to save json data to mysql?

Question

I am going to build a form customize system with Laravel 5.1 LTS. The user could create forms, customize its fields and styles, release them then the other users could fill the form.

In the front-end side, I'm going to use Alpaca Forms. With this package, fields and data are rendered with JSON.

Now the problem is how can I store fields' JSON and data' JSON to MySQL?(I don't really want to use MongoDB or other NoSQL databases, cause I'm already using MySQL now and I don't know how to deal with NoSQL.) The JSON is like this:

$("#form").alpaca({
    "data": {
        "name": "Diego Maradona",
        "feedback": "Very impressive.",
        "ranking": "excellent"
    },
    "schema": {
        "title":"User Feedback",
        "description":"What do you think about Alpaca?",
        "type":"object",
        "properties": {
            "name": {
                "type":"string",
                "title":"Name",
                "required":true
            },
            "feedback": {
                "type":"string",
                "title":"Feedback"
            },
            "ranking": {
                "type":"string",
                "title":"Ranking",
                "enum":['excellent','ok','so so'],
                "required":true
            }
        }
    },
    "options": {
        "form":{
            "attributes":{
                "action":"http://httpbin.org/post",
                "method":"post"
            },
            "buttons":{
                "submit":{}
            }
        },
        "helper": "Tell us what you think about Alpaca!",
        "fields": {
            "name": {
                "size": 20,
                "helper": "Please enter your name."
            },
            "feedback" : {
                "type": "textarea",
                "name": "your_feedback",
                "rows": 5,
                "cols": 40,
                "helper": "Please enter your feedback."
            },
            "ranking": {
                "type": "select",
                "helper": "Select your ranking.",
                "optionLabels": ["Awesome!",
                    "It's Ok",
                    "Hmm..."]
            }
        }
    },
    "view" : "bootstrap-edit"
});

I have come up with two solutions for saving front-end JSON and one solution for saving data till now, but I don't think they are good enough, so I'm here asking for help to find a better one.

Save front-end JSON:

1. list all the attributes of the front-end JSON, create a table with that and save all the value. It's not good to extend, if the package changes, I should update the table. the form field table is like:

   | id | form_id | type | name | rows | ... |

2. resolve JSON to the key-value array and save it to the database. It's not good that if a user creates a form, he will insert a lot of rows to the table. the form field table is like:

   | id | form_id | key | value |

3. save JSON as an attribute. I know Mysql 5.7 could support JSON, but I don't know if there are any other problems with this And Laravel 5.1 doesn't support JSON search. the form table is like:

   | id | JSON |

Save data JSON:

1. resolve JSON to the key-value array and save it to the database. It's not good that if a user fills a form, he will insert a lot of rows to the table. the data table is like:

   | id | form_id | key | value |

Thanks~

Answer 1

in de database have a text column.

sizes from: text storage sizes

      Type | Maximum length
-----------+-------------------------------------
  TINYTEXT |           255 (2 8−1) bytes
      TEXT |        65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (224−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (232−1) bytes =  4 GiB

then mark the json field as a cast to array in your model.

protected $casts = [
    'options' => 'array',
];

https://laravel.com/docs/5.3/eloquent-mutators#array-and-json-casting

The array cast type is particularly useful when working with columns that are stored as serialized JSON. For example, if your database has a JSON or TEXT field type that contains serialized JSON, adding the array cast to that attribute will automatically deserialize the attribute to a PHP array when you access it on your Eloquent model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model {
   /**
    * The attributes that should be casted to native types.
    *
    * @var array
    */
   protected $casts = [
       'options' => 'array',
   ]; 
} 

Once the cast is defined, you may access the options attribute and it will automatically be deserialized from JSON into a PHP array. When you set the value of the options attribute, the given array will automatically be serialized back into JSON for storage:

$user = App\User::find(1);

$options = $user->options;

$options['key'] = 'value';

$user->options = $options;

$user->save();

This way you can store your settings in individual models, and have the configuration just work itself it from the entire JSON, without maximum flexiblity.

To get it to work in your aplace you could do a simple

MyModel::selectRaw('myjson_field as json_output')->where('id','1')->get()->first()->json_output 

to bypass the casting to array.