How to Search JSON Data in Database Laravel

How to Search JSON Data in Database Laravel ?

Aman Jain,

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") > 3

and 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 json

Step 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 -m

this 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 migrate

Step 4 : Create controller

Next, Create a controller and store the details json format then fetch them using json_extract.

php artisan make:controller ArticleController

and 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","$")");
Php Laravel Laravel 9

Related Posts