SELECT a value from a column that has multiple array values-CodePudding

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?

CodePudding user response：

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"}} |
 ---------------------------------------------------------------------------

CodePudding user response：

You can use substring_index() :

select *, substring_index(SUBSTRING_INDEX(amount_detail,'amount":"',-1), '"', 1) as amount
 from Student_fees_deposit
 where id = 1;

CodePudding user response：

SELECT * FROM `Student_fees_deposit` WHERE JSON_EXTRACT(JSON_EXTRACT(amount_detail,'$."1"'), '$.amount') = "5000.00"

There's a typo in your first entry, missing ":"