Level 7
Your data looks binary to me. when I export my JSON it looks like JSON.
I think you want to cast content to json, and not an array, won't matter for this though.
Is your DB column also JSON?
Mar 20, 2023
7
Level 2
laravel search in json column
Table :: orders Json column : content
protected $casts = [
'content' => 'array',
];
example data in (content) Json column :
{"text-1606864316283":"\u062d\u0628\u064a\u0628 \u062d\u0645\u062f","text-1606864319865":"\u0627\u0644\u0645\u0627\u0646\u064a\u0629","number-1606864754766":"017655776017","textarea-1606864812668":"\u0644\u0627","textarea-1667933372850":"\u0644\u0627\u064a\u0648\u062c\u062f","textarea-1667933643727":"\u0644\u0627\u064a\u0648\u062c\u062f","textarea-1667933660719":"\u0644\u0627\u064a\u0648\u062c\u062f","textarea-1667933444030":"\u0644\u0627\u064a\u0648\u062c\u062f","text-1667932207314":"\u0631\u062c\u0627\u0621 \u0645\u0644\u0627 \u062d\u0645\u0648\u062f","text-1606864693803":"\u0633\u0648\u0631\u064a\u0627","text-1667932291997":"\u0627\u0644\u062d\u0633\u0643\u0629: \u0642\u0631\u064a\u0629 \u0627\u0644\u062a\u0648\u064a\u0646\u0629","number-1606864776968":"0985714363","textarea-1667932570890":"\u0644\u0627 \u064a\u0648\u062c\u062f","radio-group-1606864610564":"\u062f\u0627\u0626\u0645\u0629","radio-group-1661790430458":"\u0639\u0642\u062f \u0632\u0648\u0627\u062c \u0645\u0639 \u0644\u0645 \u0634\u0645\u0644","radio-group-1667932356304":"\u0646\u0639\u0645 \u0623\u0648\u0627\u0641\u0642"}
Here the data is stored from a dynamic form not static
So I stored it as json
Now when you try to search for some of the words that have been entered, the results do not appear
Knowing that the word exists, but it was converted to the following form when stored
I tried the following methods and did not find any result
Order::where('content', 'LIKE', "%" . str_replace('"', '', json_encode(request()->search)) . "%")->get(); // no result
Order::where('content', 'LIKE', "%" . request()->search . "%")->get(); // no result
Order::where('content', 'LIKE', "%". json_encode(request()->search) . "%")->get(); // no result
Order::whereJsonContains('content', json_encode(request()->search))->get(); // no result
Is there a way whatsoever for me to be able to search within this column in multiple languages
Is there any way I can search inside this column ?
Level 2
@adampatterson $table->longText('content')->fulltext()->nullable();
Level 2
@samehdev Try these options:
Order::where('content->text-1606864316283', '\u062d\u0628\u064a\u0628 \u062d\u0645\u062f')->get();
// or
Order::query()->whereJsonContains('content->text-1606864316283', '\u062d\u0628\u064a\u0628 \u062d\u0645\u062f')->get();
// or
Order::whereNotNull('content->text-1606864316283')->get();
Level 2
the key is not static (text-1606864316283) ex : text-1606864999999 text-1606864316283 text-1606864316250 is random
Level 74
@samehdev That is a really bad way to store data. I suggest giving this a read.
1 like
Level 10
@Tray2 I gave your article a read, has some good points. What would you consider a good use case for json column in a table? (outside of the user entered data in your conculsion)
I have an experience/achievements gamification type feature I am building that I was considering using json column to store the condition that are required for someone to unlock an achievement. I need to make sure the website owners who have requested this feature are able to add / edit achievements in the admin backend I am building & each achievement could have any number of steps required to unlock that achievement, so I was thinking of storing the "action" => "value" key pairs in a json column. Should i just create a seperate table for this?
Sorry I know I shouldn't hijack someone else's question, but I wanted to respond directly to your link.
Level 74
@CamKem Like I wrote in the post, If you fetch a large json payload from an api, then it's good to store it temorarily in a json column, and the process it. Other that that, I wouldn't use json columns if I can avoid it.
However there are some criteria that you should consider if choosing a json column.
- The data should be set and forget.
- You should never search for any of the key values in the json column. since you really can't index it, unless you use generated columns, but then you might as well use a regular column for that key anyway.
- The data should be read only, in other words it should never change.
- The keys should not be deleted.
These are my opinions on json columns, and it is up to you if you want to use json columns anyway, but I would never recommend it.
Please or to participate in this conversation.