Laravel MySQL 5.7 implementation of json array as rows

laravel-mysql-57-json-array-to-rows.php

<?php

\Illuminate\Support\Facades\Schema::create('z_user', function ($table) {
    $table->uuid('user_id');
    $table->json('skills');
});

\Illuminate\Support\Facades\DB::table('z_user')->insert([
    ['user_id' => \Illuminate\Support\Str::uuid(), 'skills' => '["coding", "baseball", "soccer"]'],
    ['user_id' => \Illuminate\Support\Str::uuid(), 'skills' => '["coding", "public-speaking"]'],
    ['user_id' => \Illuminate\Support\Str::uuid(), 'skills' => '["baseball", "soccer"]'],
]);

\Illuminate\Support\Facades\Schema::create('z_tmp_row', function ($table) {
    $table->integer('row');
});

\Illuminate\Support\Facades\DB::table('z_tmp_row')->insert(
    \Illuminate\Support\Collection::make(range(1, 8))->map(function ($r) {
        return ['row' => $r];
    })->toArray()
);

\Illuminate\Support\Facades\DB::statement('create view z_user_skills as select user_id, json_unquote(json_extract(skills, concat("$[", z.row, "]"))) as skill from z_user inner join z_tmp_row as z on z.row < json_length(skills)');

\Illuminate\Support\Facades\DB::table('z_user_skills')->where('skill', 'soccer')->get();