如何将包含数组的 JSON 表迁移到 SQL？
我是一名实习前端开发人员。从来没有真正喜欢过后端，所以我对 SQL 知之甚少。
但现在，在一个冒险时代，我需要做后端，因为我也被分配了。嗯，我们有一个内部系统来跟踪我们正在重建的一些关于我们自己的数据。以前的系统使用 JSON 文件来存储其数据，格式如下：
{
"id": 123,
"userId": 522,
"effortDate": "2020-06-05",
"data": [
{
"projectId": 14,
"value": 7
},
{
"projectId": 23,
"value": 3
}
],
"updatedAt": "2020-06-08T10:13:11-03:00",
"createdAt": "2020-06-08T10:13:11-03:00"
}
我们正在转向基于 SQL 的数据库。因此，根据我对关系数据库如何工作的基本了解，
我建立了这个模型：
问题是：至少现在，我们为应用程序使用相同的前端，所以我需要以与当时只是 JSON 时相同的格式解析数据。而对于系统的基本操作，它需要一直需要现在的所有数据，两张表。
我的第一个想法是，强行将它们与两个不同的 SELECT 混合在一起。我想，这并不是那么糟糕（也许是因为它有效），除了我们正在处理数千个条目，所以我担心事情可能会变慢。这是我用来解析它的代码：
public function getAllEfforts() {
/* Get all data from `effort` */
$effortQuery = "SELECT id, userId, effortDate, createdAt, updatedAt
FROM efforts;";
$efforts = $this->pdo
->query($effortQuery)
->fetchAll(PDO::FETCH_ASSOC);
/* Get all data from `effort_data` */
$effortDataQuery = "SELECT id, effortId, projectId, value
FROM efforts_data;";
$effortsData = $this->pdo
->query($effortDataQuery)
->fetchAll(PDO::FETCH_ASSOC);
/* Since, `effort` doesn't have a data field, let's define the array wished here */
foreach($efforts as &$effortsUnity) {
$effortsUnity['data'] = [];
}
/* Push the stuff from `effort_data` to the just created data array */
foreach($effortsData as &$effortDataUnity) {
$effortIndex = ($effortDataUnity['effortId'] - 1);
unset($effortDataUnity['id']);
unset($effortDataUnity['effortId']);
array_push(
$efforts[$effortIndex]['data'],
$effortDataUnity
);
}
return $efforts;
}
我的问题是：有什么办法只使用 SQL 来做到这一点吗？或者除了使用 MongoDB 或其他基于 JSON 的数据库之外的任何其他最佳方法，但目前还不可能。
抱歉，如果我不够清楚，这是我的第一个问题，我愿意澄清任何问题。
白衣染霜花2回答
-
ITMISS
您可以利用 MySQL 的 JSON 函数直接从数据库生成嵌套 JSON 结构。考虑：SELECT id, userId, effortDate, createdAt, updatedAt, ( SELECT JSON_ARRAYAGG( JSON_OBJECT('projectId', ed.projectId, 'value', ed.value) ) FROM efforts_data ed WHERE ed.effortId = e.id ) AS dataFROM efforts这为您提供了每行一行efforts，其中有一列名为data，其中包含由 table 中的对象数组组成的 JSON 有效负载efforts_data。如果您想要的话，您可以向前一步将每一行填充到一个对象中：SELECT JSON_OBJECT( 'id', id, 'userId', userId, 'effortDate', effortDate, 'createdAt', createdAt, 'updatedAt', updatedAt, 'data', ( SELECT JSON_ARRAYAGG( JSON_OBJECT('projectId', ed.projectId, 'value', ed.value) ) FROM efforts_data ed WHERE ed.effortId = e.id )) resFROM efforts在 MySQL < 5.7.22 或 MariaDB < 10.5.0 中JSON_ARRAYAGG()尚不可用，一种解决方法是GROUP_CONCAT()字符串连接。基本上你会重写这个：SELECT JSON_ARRAYAGG( JSON_OBJECT('projectId', ed.projectId, 'value', ed.value))FROM efforts_data edWHERE ed.effortId = e.id作为：SELECT CONCAT( '[', GROUP_CONCAT(JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)), ']')FROM efforts_data edWHERE ed.effortId = e.id -
慕容森
我的问题是：有什么办法只使用 SQL 来做到这一点吗？efforts id是的，这就是为什么和之间存在关系efforts_data effortId：SELECT e.id, e.userId, e.effortDate, e.createdAt, e.updatedAt, ed.id, ed.effortId, ed.projectId, ed.valueFROM efforts AS e, efforts_data AS edWHERE e.id = ed.effortId
随时随地看视频慕课网APP
PHP