I have a table called "Student_fees_deposit" and in this table, a column has multiple values in it. An example is
Student_fees_deposit
id | amount_detail
---|----------------
1 |{"1"{"amount":"5000.00","date":"2022-12-15","amount_discount":"5000.00"}}
2 | {"1":{"amount":"9000.00","date":"2022-12-15","amount_discount":"5000.00"}}
How can I get the amount value for id = 1 which is 5000.00?
SELECT JSON_EXTRACT(JSON_EXTRACT(amount_detail,'$."1"'), '$.amount') FROM `Student_fees_deposit` WHERE id = 1;
There's a typo in your first entry, missing ":"
That data type is JSON.
MariaDB supported JSON since 10.2.7 and there are a lot of functions available to use.
MySQL supported JSON since 5.7.8 (their document) and there are a lot of functions available to use.
There are some difference between them - for example: MariaDB do not support column path operator (->, ->>). So, you can only use this on MySQL.
You can use this SQL statement to get value.
SELECT JSON_VALUE(`amount_detail`, '$.*.amount') AS `extracted` FROM `your_table` WHERE `id` = 1
The difference between JSON_VALUE() and JSON_EXTRACT() is the JSON_EXTRACT() contain double quote and square brackets ([".."]) in the result. (Tested with MariaDB)
SELECT JSON_EXTRACT(`amount_detail`, '$.*.amount') AS `extracted` FROM `your_table` WHERE `id` = 1
Result (MariaDB):
["5000.00"]
SELECT JSON_VALUE(`amount_detail`, '$.*.amount') AS `extracted` FROM `your_table` WHERE `id` = 1
Result:
5000.00
Sample code in PHP.
$sql = 'SELECT *, JSON_VALUE(`amount_detail`, \'$.*.amount\') AS `extracted` FROM `your_table` WHERE `id` = 1';
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetchAll();
foreach ($result as $row) {
echo 'ID: ' . $row->id;
echo ' ; ';
echo 'amount: ' . $row->extracted;
echo '<br>';
}
You can use substring_index() :
select *, substring_index(SUBSTRING_INDEX(amount_detail,'amount":"',-1), '"', 1) as amount
from Student_fees_deposit
where id = 1;
Does this answer your question?
SELECT * FROM student_fees_deposit WHERE id = 1 AND amount_detail LIKE '%"amount": "5000.00"%';
In reality, you aren't supposed to store JSON in your database. I'd rather put 3 individual columns (amount, date, amount_discount) instead of storing JSON, just because of how difficult it is to run complex queries on JSON data, plus it tends to break rules of normalization.
You can easily restructure your table and make it more maintainable, more structure and overall objectively better by just splitting up the JSON into individual columns.
Another thing you should avoid in the future is storing Arrays in your database as that can be easily implemented with 1:M relation which also sort of enforces normalization rule number 3.
Here is a working test I tried on my own database:
SELECT _message FROM _messages WHERE _message LIKE '%"amount":"5000.00"%';
+---------------------------------------------------------------------------+
| _message |
+---------------------------------------------------------------------------+
| {"1"{"amount":"5000.00","date":"2022-12-15","amount_discount":"5000.00"}} |
+---------------------------------------------------------------------------+
