Level 73
I advice against using the database to store arrays/json strings like that, it kinda defeats the purpose of a relational database.
Jun 12, 2025
11
Level 1
Edit the last value of an array and put back into database
Hi,
I added an array in a database column and I am trying to edit the last value and insert it back into the database with the new value but its not working. Here is my code:
This is the data that is stored in the database. I am trying to change the last array so the "27.5.24" and "34".
[
[
"24.5.24", "100"
],
[
"25.5.24", "58"
],
[
"26.5.24", "49"
],
[
"27.5.24", "34"
]
}
This is the code that changes the value:
namespace App\Http\Controllers;
use App\Models\Keyword;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\RedirectResponse;
use Illuminate\Support\Facades\Redirect;
class KeywordController extends Controller
{
public function updateKeyword(string $keyword_id): RedirectResponse
{
$keyword = Keyword::find($keyword_id);
$data = $keyword->data;
if (!empty($data)) {
$data[count($data)-1][0] = "10.10.10";
$data[count($data)-1][1] = "150";
}
$keyword->data = $data;
$keyword->save();
}
}
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Keyword extends Model
{
use HasFactory;
protected $fillable = [
'keyword',
'projects_id',
'data',
];
protected function casts(): array
{
return [
'data' => 'array',
];
}
When I run the code, the data remains unchanged. Can someone provide some advice?
Thank you.
Level 1
@Tray2 Thank you for the advice. The reason why I am storing this data as json/array is because I will be using this data with the DataTables Library. Since its already in the right format, all I need to do is retrieve the array and then I can use the array to display a table using DataTables. All these values will just be historical data and the only values that I will need to edit is the last value. I will also be adding new values to the array everyday.
If I create a row for each data point, I will need a new row everyday which will be 365 rows a year and the rows can add up quickly as we will have lots of keywords. I will also need to turn the data points into an array for DataTables. I also have another column which is the project id which will deal with the relational aspect of the querying. Therefore, i decided to store this as arrays/json strings.
I understand that its generally not a good idea to store things as array/json in the database but through my research, I think its okay in my case? I am quite new to this so if you have any advice for me, please let me know.
Thank you.
Level 73
@MasterLava I would say no, the database can handle a shitload of rows, and with the proper indexes you will be fine up to millions of records, after that there are ways to divide the tables into partitions, those can be divided by for example year.
According to Google, in InnoDB, with a limit on table size of 64 terabytes and a MySQL row-size limit of 65,535 there can be 1,073,741,824 rows. That would be the maximum number of records, each utilizing the maximum row-size limit. However, more records can be added if the row size is smaller.
Level 80
Thank you for the advice. The reason why I am storing this data as json/array is because I will be using this data with the DataTables Library.
@MasterLava Your UI and individual libraries should not be dictating your database schema.
Store your data properly (i.e. relationally in a relational database), and then format the data in your controller to present it to however your UI or a particular library needs it. As what happens if you want to say, show graphs, and that expects data in a completely different format…? You’re screwed.
1 like
Level 30
You should do this:
$keyword->forceFill( [ 'data' => $data ] );
This is some specificity of Laravel when it deals with JSON columns.
Level 1
@Glukinho It worked, thank you very much.
Level 122
so you have pairs of date and count.
How long will this continue?
How large will the column get over time.
How will you start to trim the size of the field over time?
There is absolutely no reason why this data cannot be in a seperate table and loaded as a relation.
Level 20
@Snapey And depending on the original column type, the length could become a real issue.
We've just hit this snag at $dayJob where the column was LongText and the data stored in it was JSON (there are legit reasons for this being JSON data as there's no fixed structure... however, IMO, it leans more to the fact that this particular data is being stored in the wrong type of database)... and a few of the entries were too long for the column so it became truncated. This in itself is far from ideal of course, but being JSON, it now meant that not only was all of the data not there, what was there couldn't be retrieved and parsed as it was now invalid JSON.. this can easily happen with the array data here where you mention how much it could potentially grow.
Just thought I'd throw in a real-world issue highlighting this.
Level 30
In all cases when I thought "Ah, I can leave this data in unstructured JSONs instead of proper relational way" I very soon came to decision to rebuild it properly, it was double work. 100% of cases.
1 like
Level 75
The reason why I am storing this data as json/array is because I will be using this data with the DataTables Library
I suggest not use DataTables, but learn how to code a table yourself.
With a little learning curve you can even do things like in place editing , add a row on the fly, etc. But it is worth going through the learning curve to know this stuff.
And I agree with @tray2 learn how to let the database do it's job.
1 like
Level 22
According to Google, in InnoDB, with a limit on table size of 64 terabytes and a MySQL row-size limit of 65,535 there can be 1,073,741,824 rows. That would be the maximum number of records, each utilizing the maximum row-size limit. However, more records can be added if the row size is smaller.
I would also like to point out that in addition to what @tray2 said, storing JSON (or even worse XML) in the database will cause issues, real issues once you get to a point where you have substantial amounts of data.
Specifically, when storing JSON (or XML) in the database, you will end up eating up storage space, relational databases are hyper optimized to store data directly in columns, when you put JSON into a varchar/nvarchar or text column you are inherently storing more data (all of the brackets, spaces, tabs, etc) are literally being stored into the databases data files. Whereas in your example all you are trying to store is a date (between 3-8 bytes) and integer (4 bytes) combination... each character of your JSON/ARRAY is 4 bytes so do the math...
At my job, there were previous developers who stored XML directly into the database, and a table that would normally be somewhere in the range of 100-200 MB ended up being well over 10GB... And if you happen to be taking backups of your database, each backup will reflect the much larger size of the data within the various tables.
Additionally, storing JSON in tables will end up decreasing performance because MOST relational DBMS' will try to use statistics to determine the fastest way to access the data you query for, but in most cases that is not possible with string data (varchar/nvarchar/text).
Store your data properly (i.e. relationally in a relational database), and then format the data in your controller to present it to however your UI or a particular library needs it.
@martinbean 's advice (quoted above) is probably one of the best pieces of advice I have heard all year. Do not store data in DBs based on how a package might need/want the data, store it efficiently and then use your controller to format the data how the view needs it.
2 likes
Please or to participate in this conversation.