Question 21536 in Mysql Question posted in
The official MySQL documentation can be found here.

SELECT a value from a column that has multiple array values – Mysql

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?

Tags:

4

Answers


  1. 0

    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"}} |
+---------------------------------------------------------------------------+
    Login or Signup to reply.
  2. 0

    You can use substring_index() :

    select *, substring_index(SUBSTRING_INDEX(amount_detail,'amount":"',-1), '"', 1) as amount
 from Student_fees_deposit
 where id = 1;
    Login or Signup to reply.
  3. 0 
    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 ":"

    Login or Signup to reply.
  4. 0

    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>';
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search