JSON Data in database is used to store the informational data. JSON can be easily parse and stringify so it can be stored easily in database. In this post i will show you to search JSON data in database laravel. Laravel by default configured with MySQL and we will store the json string in MySQL database.
While developing an application sometime we store data in raw format rather then in relational format so to search this type of data is difficult but using the MySQL version >= 5.7 supports JSON_EXTRACT method to search in json string as follow
SELECT JSON_EXTRACT(json, "$.id") AS name
FROM table
WHERE JSON_EXTRACT(json, "$.id") > 3and in laravel
Model::whereRaw("json_extract('json', '$.id')", '<', 3)->get()json_encode function is used to convert a array to a json string, so that it can be stored in a file, memory, database and also can transferred between the different networks. to save json we need to store it in string format.
This example will work any version of laravel 5, laravel 6, laravel 7, laravel 8 and laravel 9.
You can learn about How to Store JSON in Database Laravel ?
Let’s understand Search JSON Data in Database Laravel with multiple examples so complete basic setup as follow
Step 1: Create a laravel project
First step is to create the Laravel project using composer command or you can also read the How to install laravel 8 ? and Laravel artisan command to generate controllers, Model, Components and Migrations
composer create-project laravel/laravel jsonStep 2: Configure database
Next step is to configure the database for our project, table and data. So open .env or if file not exist then rename .env.example file to .env file in root folder and change below details
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=Step 3 : Create Model and Migrations
Now, create a table article and migrations as follow
php artisan make:model Article -mthis will generate the model and migration file
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Article extends Model
{
use HasFactory;
}and migration file database/migrations/timestamp_create_articles_table.php
public function up()
{
Schema::create('articles', function (Blueprint $table) {
$table->id();
$table->string('email')->unique();;
$table->string('title');
$table->string('body')->nullable();
$table->text('json_details')->nullable();
$table->string('image');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('articles');
}
and then migrate the migration
php artisan migrateStep 4 : Create controller
Next, Create a controller and store the details json format then fetch them using json_extract.
php artisan make:controller ArticleControllerand add the below code
<?php
namespace App\Http\Controllers;
use App\Models\Article;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function getJson(Request $request)
{
return Article::selectRaw("JSON_EXTRACT(json_details,'$.id')")->whereRaw("JSON_EXTRACT(json_details,'$.id') > 1");
}
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$validations=[
'title' => "required",
'email' => "required|email|unique:articles,email",
'json' => "required",
];
$validator = Validator::make($request->all(), $validations);
if ($validator->fails()) {
return response()->json($validator->errors(),422);
}
// save image and name in database
$Article = new Article();
$Article->title = $request->title;
$Article->email = $request->email;
$Article->json_details = json_encode(["id"=>1,"name"=>"test"]);
$Article->save();
return response()->json(["status" => true,"data"=>$Article, "message" => "You have successfully created the article."]);
}
}
Step 4: Create routes in routes/api.php
Create route for store and get json
routes/api.php
<?php
use Illuminate\Support\Facades\Route;
Route::get('articles', '\App\Http\Controllers\ArticleApiController@getJson');
Route::post('articles', '\App\Http\Controllers\ArticleApiController@create');So here we used json encode to store the data in database and json_extract MySQL method to extract json in MySQL.
Let’s take multiple ways to search in json as below
Method 1 : Json search in JSON object
In this method we will store json object and will search it. Suppose we have below table in database
{"id":1,"name":"test"}then we can use as below as MySQL query
select JSON_EXTRACT(json_details,"$.name") as name from table where JSON_EXTRACT(json_details,"$.name") = "test"And in laravel
Article::selectRaw("JSON_EXTRACT(json_details,'$.name') as name")->whereRaw("JSON_EXTRACT(json_details,'$.id') ='test' "); Method 2: Json search in JSON Array object
In this method we will store json array object and will search it. Suppose we have below table data in database
[{"id":1,"name":"test"},{"id":2,"name":"test2"}]then we can use as below as MySQL query
select JSON_EXTRACT(json_details,"$[*].id") as id from table where JSON_CONTAINS(JSON_EXTRACT(json_details,"$[*].id"),"1","$") And in laravel
Article::selectRaw("JSON_EXTRACT(json_details,"$[*].id") as id")->whereRaw("JSON_CONTAINS(JSON_EXTRACT(json_details,"$[*].id"),"1","$") "); Method 3: Json search in JSON Array Only
In this method we will store json array and will search it. Suppose we have below table data in database
[1,2,3,4]then we can use as below as MySQL query
select JSON_EXTRACT(json_details,"$[0]") as id from table where JSON_CONTAINS(json_details,"1","$") And in laravel
Article::selectRaw("JSON_EXTRACT(json_details,"$[0]") as id")->whereRaw("JSON_CONTAINS(json_details,"1","$")");