Skip to content
Readerstacks logo Readerstacks
  • Home
  • Softwares
  • Angular
  • Php
  • Laravel
  • Flutter
Readerstacks logo
Readerstacks
How to Search JSON Data in Database Laravel

How to Search JSON Data in Database Laravel ?

Aman Jain, August 2, 2022March 16, 2024

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","$")");   

Related

Php Laravel Laravel 9 laravelSearch

Post navigation

Previous post
Next post

Related Posts

Softwares Traits-in-Php

What is a traits in PHP and how to use it ?

June 20, 2021November 8, 2023

Before PHP 5.4, there was no way to implement multiple inheritance in PHP then PHP 5.4 introduce traits as a substitute of multiple inheritance in PHP. By using the traits PHP solve the problem of Multiple Inheritance. A class only extend one class what if we wanted to use multiple…

Read More
Php Laravel slug generator using trait

Laravel reusable slug generator using trait

November 8, 2021November 8, 2021

In our last article Laravel slug generator with example we demonstrate how we can generate slug for model but in that article we need to rewrite the same code for multiple models if we want use the slug generation in multiple model. so in this article i will show you…

Read More
Php Laravel pagination with search

How to use Laravel pagination with search ?

February 22, 2022November 19, 2023

In this tutorial we will learn pagination in Laravel. Laravel provides its own library to build the pagination html, which we can easily use in our html page using $model->links() method and $model->paginate() method to make a long list into pagination. Thus in this tutorial i will show you to…

Read More

Aman Jain
Aman Jain

With years of hands-on experience in the realm of web and mobile development, they have honed their skills in various technologies, including Laravel, PHP CodeIgniter, mobile app development, web app development, Flutter, React, JavaScript, Angular, Devops and so much more. Their proficiency extends to building robust REST APIs, AWS Code scaling, and optimization, ensuring that your applications run seamlessly on the cloud.

Categories

  • Angular
  • CSS
  • Dart
  • Devops
  • Flutter
  • HTML
  • Javascript
  • jQuery
  • Laravel
  • Laravel 10
  • Laravel 11
  • Laravel 9
  • Mysql
  • Php
  • Softwares
  • Ubuntu
  • Uncategorized

Archives

  • June 2025
  • May 2025
  • April 2025
  • October 2024
  • July 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • July 2023
  • March 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021

Recent Posts

  • The Resilience of Nature: How Forests Recover After Fires
  • Understanding Laravel Cookie Consent for GDPR Compliance
  • Understanding High Vulnerabilities: A Critical Overview of the Week of May 12, 2025
  • Installing a LAMP Stack on Ubuntu: A Comprehensive Guide
  • Understanding High Vulnerabilities: A Deep Dive into Recent Security Concerns
©2023 Readerstacks | Design and Developed by Readerstacks
Go to mobile version