En mi sitio de trabajo tenían la necesidad de usar Google Maps para geo-referenciar sectores con latitudes y longitudes y otras características mas, pero como las respuestas son en formato JSON (ya que es un API), estuve investigando ya que había muchas columnas en la tabla y no quería hacer otra tabla solo para esos valores, vi que existía en MySQL y MariaDB un campo tipo json para guardar data en formato json y lo mas maravilloso es hacer consultas where para buscar data en especifico.
Laravel, como un frameworks tan potente y fácil de usar vi que lo emplea de forma fácil y sencilla, aquí en este corto tutorial le explicare como usar de forma correcta este maravilloso tipo de dato que trae MySQL.
Requisito
Debes tener instalado MySQL v5,7,8 o superior en su entorno de desarrollo, si está usando MariaDB, los campos JSON serán compatibles con MariaDB v10.2 en adelante.
Creando la migración de datos
En este ejemplo vamos a usar la migración que trae Laravel por defecto de create_users_table y agregamos una columna llamada “meta” que se va a encargar de guardar todos los metas en el campo tipo JSON:
1 2 3 4 5 6 7 8 9 10 11 | public function up() { Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('email')->unique(); $table->string('password'); $table->rememberToken(); $table->json('meta')->nullable(); $table->timestamps(); }); } |
Después ejecutamos el “php artisan migrate” para crear la tabla users.
Perfil de usuario con el campo meta
Este es un ejemplo de los metas que va a tener nuestro usuarios:
1 2 3 4 5 6 7 8 9 | { "gender": "Masculino", "country": "Venezuela", "bio": { "summery": "Lorem ipsum dolor sit amet, consectetur adipisicing elit. A labore voluptatem ipsam velit alias, rem in voluptates, suscipit sapiente est.", "full": "Lorem ipsum dolor sit amet, consectetur adipisicing elit. Inventore est dolor, et optio sint nulla amet consequatur assumenda excepturi eius! Lorem ipsum dolor sit amet, consectetur adipisicing elit. Inventore est dolor, et optio sint nulla amet consequatur assumenda excepturi eius! Eos delectus in architecto dolor blanditiis adipisci laudantium repellendus error." }, "skills": ["PHP", "Laravel", "MySQL", "VueJS", "JavaScript", "JSON"] } |
Puedes agregar la cantidad de data que desees en el campo meta, lo puedes ampliar si es necesario.
Model User
Ahora tenemos que convertir los datos de tipo JSON que están en la base de datos a array de esta forma:
1 2 3 4 | protected $casts = [ 'id' => 'int', 'meta' => 'array' ]; |
Ahora agregamos el campo meta en el $fillable
1 2 3 4 5 6 | protected $fillable = [ 'name', 'email', 'password', 'meta' ]; |
Factory User
Antes que podamos seguir con la columna de tipo json vamos a crear un factory User para agregar algunos datos de prueba:
1 2 3 4 5 6 7 8 9 10 11 | $factory->define(App\User::class, function (Faker\Generator $faker) { static $password; return [ 'name' => $faker->name, 'email' => $faker->unique()->safeEmail, 'meta' => [ "gender" => $faker->randomElement(['Male', 'Female']), "country" => $faker->country, "bio" => [ "summery" => $faker->realText(), "full" => $faker->realText(800) ], "skills" => $faker->randomElements(["PHP", "Laravel", "MySQL", "VueJS", "JavaScript", "JSON"], rand(2,4)) ], 'password' => $password ?: $password = bcrypt('secret'), 'remember_token' => str_random(10), ]; }); |
Seed User
creamos el seeder de esta manera:
1 | php artisan make:seeder UserSeeder |
Ahora agregamos el factory que creamos en el UserSeeder en el método run():
1 | factory(App\User::class, 60)->create(); |
Luego en DatabaseSeeder llamamos el seeder UserSeeder en el método run():
1 | $this->call(UserSeeder::class); |
Ahora tenemos algunos datos de ejemplo para poderlo usar ahora vamos hacer un crud.
Accesor para campos JSON
Supongamos que queremos acceder a todas la habilidades del usuario, definimos el acceso y agregamos estas habilidades a la matriz $appends del modelo, por lo que se agregara como una columna en las serializaciones JSON y de los arreglos:
1 2 3 4 5 | protected $appends = ['skills']; public function getSkillsAttribute() { return array_get($this->meta, 'skills', []); } |
Eso es todo, ahora puedes acceder a habilidades $user→skills como una propiedad en el modelo de usuarios.
Insertar datos
Gracias a Laravel, la creación de atributos, la serialización y la deserialización de columnas JSON son sencillas, hemos creado el campo meta como una matriz, ahora insertar datos en este campo JSON ya es simple.
Para este caso vamos a usar Tinker para insertar datos:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | >>> $data = factory(App\User::class)->make(['password' => bcrypt('secret')])->makeVisible('password'); => App\User {#706 name: "Gustavo Pino", email: "gpino@example.com", meta: "{"gender":"Male","country":"Oman","bio":{"summery":"Dodo solemnly, rising to its feet?' 'In my youth,' said the Mock Turtle"},"skills":["Laravel","MySQL","VueJS","PHP"]}", password: "$2y$10$K4RTr5dP/lTbMVUqL.YB5.iut0DYOTvoDDULwDqbtXMaLQXmgymyW", } >>> App\User::create($data->toArray()) => App\User {#671 name: "Gustavo Pino", email: "gpino@example.com", meta: "{"gender":"Male","country":"Oman","bio":{"summery":"Dodo solemnly, rising to its feet?' 'In my youth,' said the Mock Turtle"},"skills":["Laravel","MySQL","VueJS","PHP"]}" updated_at: "2017-06-29 03:25:10", created_at: "2017-06-29 03:25:10", id: 63, } |
Aquí vemos que creamos de forma satisfactoria el usuario con la data meta
Seleccionar datos del campo JSON
Ahora veremos los distintos métodos para seleccionar datos de tipo JSON.
Intentemos seleccionar usuarios de un país y por genero
1 2 3 4 5 | # To get all user from Venezuela App\User::where('meta->country', 'Venezuela')->get(); # To get all the female users App\User::where('meta->gender', 'Male')->get(); |
como puedes ver podemos acceder a los datos que esta en el JSON por medio de punteros “→” .
También puede usar el operador LIKE para realizar la búsqueda.
1 2 | # Get all the user who has PHP in skills App\User::where('meta->skills', 'like', '%PHP%')->get() |
MySQL 5.7 proporciona algunas funciones que puede usar para realizar una búsqueda en documentos JSON.
La función JSON_CONTAINS () acepta el campo JSON que se busca y otro para comparar. Devuelve 1 cuando se encuentra una coincidencia:
1 2 | # Get all the user skilled with Laravel App\User::whereRaw('JSON_CONTAINS(meta->"$.skills", \'["Laravel"]\')')->get(); |
Otra forma es la función JSON_SEARCH () que devuelve la coincidencia dada o NULL cuando no hay coincidencia. Se pasa el documento JSON que se busca, ‘uno’ para encontrar el primer partido o ‘todo’ para encontrar todas las coincidencias y una cadena de búsqueda.
1 2 | # Get all the user skilled with PHP App\User::whereRaw('JSON_SEARCH(meta->"$.skills", "one", "PHP%") IS NOT NULL')->get(); |
También puede seleccionar una ruta JSON como alias de columnas como su forma de columnas normales.
1 2 | # Selecting json path as columns App\User::select('id', 'name', 'meta->gender as sex', 'meta->skills as skills')->get(); |
Comparación y pedido de valores JSON
Los valores JSON se pueden comparar utilizando los operadores =, <, <=,>,> =, <>,! = Y <=>.
Actualizar datos de campos JSON
Ahora vamos hacer la actualización de nuestro campo tipo JSON
1 2 3 4 | // Update JSON data $user = App\User::first(); $user->meta = [ 'bio' => [ 'full' => 'Lorem ipsum dolor sit amet...' ] ]; $user->save(); |
Cuando hacemos la consulta de la data que modificamos nos trae esto:
1 | {"bio":{"full":"Lorem ipsum dolor sit amet..."}} |
Tenga en cuenta que la asignación anterior reemplazará el contenido del campo meta, si desea actualizar solo una parte del documento JSON, debe hacerlo en su lugar.
Actualización de una parte del documento JSON
Si queremos actualizar un valor especifico en el campo meta tenemos que hacer lo siguiente:
1 2 3 4 5 | # Get the user $user = App\User::find(2); # Update bio using attribute manipulation $user['meta->bio->summery'] = 'New sort bio goes here, pretty neat hah!' $user->save(); |
También lo podemos hacer de esta forma:
1 2 3 4 5 6 7 | # Get the user $user = App\User::find(2); # This works and updates table $user->forceFill(['meta->bio->summery' => 'Event newer bio goes here, pretty dope hah!']) # Save the changes $user->update() |
También puede usar la forma clásica que está en la documentación de Laravel.
Además de estos, hay algunas funciones nativas proporcionadas por MySQL 5.7.8 para manipular el documento JSON. Aquí hay unos ejemplos:
1 2 3 4 5 6 | JSON_SET(doc, path, val[, path, val]…) --inserta o actualiza datos en el documento JSON_INSERT(doc, path, val[, path, val]…) --inserta datos en el documento JSON_REPLACE(doc, path, val[, path, val]…) --reemplaza datos en el documento JSON_MERGE(doc, doc[, doc]…) --fusiona dos o más documentos JSON_ARRAY_APPEND(doc, path, val[, path, val]…) --agrega valores al final de una matriz JSON_ARRAY_INSERT(doc, path, val[, path, val]…) --inserta una matriz dentro del documento |
Borrar ruta del documento JSON
Vamos a ver como hacemos para borrar datos en un campo JSON. Lo hacemos en esta forma:
1 2 3 | // Remove skills from a user App\User::where('id', 17)->update(['meta' => DB::raw('JSON_REMOVE(meta, "$.skills")')]); |
Conclusion
Al usar JSON archivado en MySQL 5.7.8 podemos utilizar la flexibilidad de la base de datos no relacional dentro de MySQL, y Laravel, como siempre hace que sea muy fácil trabajar con campos JSON, es tentador para el nuevo comercio usar el campo JSON para todo, lo ideal es que siempre se use solo para almacenar datos como las preferencias del usuario, la configuración, las estadísticas de los usuarios o cualquier otro dato. Espero que le haya servido de ayuda!
Referencia
http://www.qcode.in/use-mysql-json-field-in-laravel/