用PHP查询多维Json

Question

我有一个Mysql查询，输出如下示例所示。

当它们是同一时，我需要将这些数据转换为Json，而不重复名称、日期和pruchase_id

示例查询：

Name        Date        Purchase_ID Product_id  Product_Qtty
Jane        2020-02-05  1523        12563       9
Jane        2020-02-05  1523        3474        1
Jane        2020-02-05  1523        66634       4
Jane        2020-03-22  5320        34374       1
Jane        2020-03-22  5320        1234        3
Jane        2020-06-15  6893        12634       4
Jane        2020-06-15  6893        13004       1
Paul        2020-02-12  1523        10560       2
Paul        2020-02-12  1523        4454        8
Paul        2020-02-12  1523        66634       7
Paul        2020-03-21  5320        4475        2
Paul        2020-03-21  5320        4563        4

Expected Json不是一个简单的Json，表中的每一行都是Json中的对象或数组，我需要像"；Jane"成为以"0"开头的所有行的密钥级别；Jane"：

{
"user":[
{
"name":"Jane",
"purchases":[
{
"Purchase_ID": 1523,
"products":[{
"product_id":12563,
"product_qtty":9
},{
"product_id":3474,
"product_qtty":1
},{
"product_id":66634,
"product_qtty":4
}
]
},{
"Purchase_ID": 5320,
"products":[{
"product_id":3474,
"product_qtty":1
},{
"product_id":1234,
"product_qtty":3
}
]
}
]} // "... and etc"
]}

更新我的代码：

<?php
require_once("includes/db_connect.php");

$link = mysqli_connect(DB_HOST, DB_USER, DB_PASS);
mysqli_select_db($link, DB_NAME);

$query = "SELECT * from User_sales";

$sth = mysqli_query($link, $query);
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
$rows[] = $r;
}
print json_encode($rows);

Answer 1

如果您的表如下所示：

CREATE TABLE user_sales (name varchar(10), `date` date, Purchase_ID int, Product_id int, Product_Qty int);
INSERT INTO user_sales VALUES
('Jane','2020-02-05',  1523,        12563,       9),
('Jane','2020-02-05',  1523,        3474 ,       1),
('Jane','2020-02-05',  1523,        66634,       4),
('Jane','2020-03-22',  5320,        34374 ,      1),
('Jane','2020-03-22',  5320,        1234  ,      3),
('Jane','2020-06-15',  6893,        12634,       4),
('Jane','2020-06-15',  6893,        13004,       1),
('Paul','2020-02-12',  1523,        10560,       2),
('Paul','2020-02-12',  1523,        4454 ,       8),
('Paul','2020-02-12',  1523,        66634,       7),
('Paul','2020-03-21',  5320,        4475 ,       2),
('Paul','2020-03-21',  5320,        4563 ,       4);

你这样做：

SELECT 
u2.`name`,
u2.`Purchase_ID`, 
JSON_ARRAYAGG(JSON_OBJECT( 'product_id', u1.`Product_Id`, 'product_qty', u1.`Product_Qty`)) as `products`
FROM user_sales u1
INNER JOIN user_sales u2 ON u1.`name`=u2.`name` and u1.`Purchase_ID`=u2.`Purchase_ID`
GROUP BY u2.`name`, u2.`Purchase_ID`

输出为：

+ --------- + ---------------- + ------------- +
| name      | Purchase_ID      | products      |
+ --------- + ---------------- + ------------- +
| Jane      | 1523             | [{"product_id": 12563, "product_qty": 9}, {"product_id": 3474, "product_qty": 1}, {"product_id": 66634, "product_qty": 4}, {"product_id": 12563, "product_qty": 9}, {"product_id": 3474, "product_qty": 1}, {"product_id": 66634, "product_qty": 4}, {"product_id": 12563, "product_qty": 9}, {"product_id": 3474, "product_qty": 1}, {"product_id": 66634, "product_qty": 4}] |
| Jane      | 5320             | [{"product_id": 34374, "product_qty": 1}, {"product_id": 1234, "product_qty": 3}, {"product_id": 34374, "product_qty": 1}, {"product_id": 1234, "product_qty": 3}] |
| Jane      | 6893             | [{"product_id": 12634, "product_qty": 4}, {"product_id": 13004, "product_qty": 1}, {"product_id": 12634, "product_qty": 4}, {"product_id": 13004, "product_qty": 1}] |
| Paul      | 1523             | [{"product_id": 10560, "product_qty": 2}, {"product_id": 4454, "product_qty": 8}, {"product_id": 66634, "product_qty": 7}, {"product_id": 10560, "product_qty": 2}, {"product_id": 4454, "product_qty": 8}, {"product_id": 66634, "product_qty": 7}, {"product_id": 10560, "product_qty": 2}, {"product_id": 4454, "product_qty": 8}, {"product_id": 66634, "product_qty": 7}] |
| Paul      | 5320             | [{"product_id": 4475, "product_qty": 2}, {"product_id": 4563, "product_qty": 4}, {"product_id": 4475, "product_qty": 2}, {"product_id": 4563, "product_qty": 4}] |
+ --------- + ---------------- + ------------- +

在这之后，将其扩展到另一个级别应该不会太复杂，(我留给您完成。(

附言：这是用MySQL 8.0 测试的