从包含多个数组值的列中选择一个值

Question

我有一个名为" student_fees_deposit "的表;在这个表中，一列有多个值。例如

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

如何获得id = 1的金额值，即5000.00?

Answer 1

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

第一个条目中有一个错别字，缺少":">

Answer 2

该数据类型为JSON。
MariaDB从10.2.7开始支持JSON，并且有很多函数可供使用。
MySQL从5.7.8开始支持JSON(他们的文档)，并且有很多函数可供使用。

它们之间有一些区别-例如:MariaDB不支持列路径操作符(->,->>)。所以，你只能在MySQL上使用。

您可以使用此SQL语句获取值。

SELECT JSON_VALUE(`amount_detail`, '$.*.amount') AS `extracted` FROM `your_table` WHERE `id` = 1

JSON_VALUE()与JSON_EXTRACT()的区别在于结果中的JSON_EXTRACT()包含双引号和方括号([".."])。(使用MariaDB测试)

SELECT JSON_EXTRACT(`amount_detail`, '$.*.amount') AS `extracted` FROM `your_table` WHERE `id` = 1

结果(MariaDB):

("5000.00")

SELECT JSON_VALUE(`amount_detail`, '$.*.amount') AS `extracted` FROM `your_table` WHERE `id` = 1

结果:

5000.00

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>';
}

Answer 3

您可以使用substring_index():

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

Answer 4

这回答你的问题了吗?

SELECT * FROM student_fees_deposit WHERE id = 1 AND amount_detail LIKE '%"amount": "5000.00"%';

实际上，您不应该在数据库中存储JSON。我宁愿放3个单独的列(amount,date,amount_discount)而不是存储JSON，只是因为在JSON数据上运行复杂的查询是多么困难，加上它往往会打破规范化规则。

你可以很容易地重组你的表，使它更易于维护，更结构化，整体客观上更好，只要把JSON分成单独的列。

另一件你将来应该避免的事情是在你的数据库中存储Arrays，因为这可以很容易地实现1:M关系，这也有点强制规范化规则3。

下面是我在自己的数据库上进行的测试:

SELECT _message FROM _messages WHERE _message LIKE '%"amount":"5000.00"%';
+---------------------------------------------------------------------------+
| _message                                                                  |
+---------------------------------------------------------------------------+
| {"1"{"amount":"5000.00","date":"2022-12-15","amount_discount":"5000.00"}} |
+---------------------------------------------------------------------------+