Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

vilom10252's avatar

How to search for specific data inside a JSON data at table

I have a table named carts which has this structure:

1	crt_id Primary	bigint(20)		
2	crt_mbr_id	    bigint(20)			
3	crt_session_id	varchar(128)	
4	crt_content	    text	
5	crt_send_type	int(11)			
6	crt_completed	tinyint(1)			
7	created_at	    timestamp			
8	updated_at	    timestamp

And crt_content data goes like this:

[{"id":"24","quantity":"1","price":3000,"discounted":3000,"coupon":0}]

Now I need to search in crt_content for the number 24. But I don't know what is the sql command for this.

So would you mind helping me out on how to search for specific data inside a JSON data which is stored at the table?

0 likes
6 replies
MohamedTammam's avatar

First you want to search of a pattern you can use LIKE keyword.

Cart::where('crt_content', 'LIKE', '%' . 24 . '%')->get();

But if you want to search for a property value inside that JSON object you can use whereJsonContains

Cart::whereJsonContains('to->crt_content->id', 24)->get();

For more information read the docs: https://laravel.com/docs/8.x/queries#json-where-clauses

EDIT

As @sinnbeck and @tray2 mentioned, you need to change your column type to be json instead of text. And it's a bad decision to use JSON columns in your SQL database and do searching against that column.

1 like
Sinnbeck's avatar

Why are you storing json data in a text field? Should be a json field. And why not make a new table with the data that just refers to this table. Will make it alot easier to work with

2 likes
Tray2's avatar

@Sinnbeck Agreed.

It's almost like adding support for json fields was a bad decision since it is used in the wrong way.

Tray2's avatar
Tray2
4 years ago
Best Answer
Level 73

This is an extremely bad design in my opinion. You should not use json_columns to store anything you need to search on.

While using json might appear as a quick solution now, it surely will not be quick in the long run.

It will also affect the performance of the database since you can't really index a json column.

Let just say that you want to get a list of all the users that have their birthday between two dates.

This is pretty easy to do with a regular field and if you have thousands of users it will slow down and to speed it back up again you add an index one the date of birth field.

To do something similar for a json column you need to use stored generated columns which I don't think is supported by laravel's migration.

https://stackoverflow.com/questions/38389075/how-to-create-index-on-json-column-in-mysql

So basically you still need to create every single field anyway in your migration or at least the ones you need to index.

I will try to make you understand that json columns might seem like a simple and good solution but it's generally not a good one or a simple one. There are no shortcuts to a good database design and that a bad decision now will affect your application in for a long time. It's much better to make a good design now that scales well and can be made more performant in more ways than just throwing money at the problem. You also need to think a bit about the future. I a year new demands or needs has been requested from the users and you need to extract/add/remove/modify some part of the application.

I suggest the you read the best answer on this stackoverflow thread

https://stackoverflow.com/questions/33437940/json-column-or-traditional-columns

3 likes

Please or to participate in this conversation.