时间戳

首页 > 解决方案 > MySQL如何将表作为子数组加入？

mysql - MySQL如何将表作为子数组加入？

问题描述

所以我有两个表，我想运行一个查询，将Receipt表和Receipt_Item表连接起来，使Receipt_Item表是一个子数组，如下图所示。

Receipts - 

| receipt_id | phone      | amount | status  |
|------------|------------|--------|---------|
| 1          | 7777777777 | 5682   | Success |
| 2          | 8888888888 | 4586   | Success |
| 3          | 5555555555 | 7589   | Success |

Receipt_Item -

| receipt_id | item_id | quantity | price |
|------------|---------|----------|-------|
| 1          | 1       | 23       | 5682  |
| 1          | 2       | 30       | 5682  |
| 2          | 1       | 10       | 7589  |
| 3          | 1       | 23       | 4355  |
| 3          | 2       | 41       | 3665  |

预期产出 -

[
    {
        "receipt_id": 1,
        "phone": "7777777777",
        "amount": "5682",
        "status": "Success",
        "receipt_item" : [
            {
              "receipt_id" : 1,
              "item_id" : 1,
              "quantity" : 23,
              "price" : 5682
            },
            {
              "receipt_id" : 1,
              "item_id" : 2,
              "quantity" : 30,
              "price" : 5682
            }

        ]
    },
    {
        "receipt_id": 2,
        "phone": "8888888888",
        "amount": "4586",
        "status": "Success",
        "receipt_item" : [
            {
              "receipt_id" : 2,
              "item_id" : 1,
              "quantity" : 10,
              "price" : 7589
            }
        ]
    },
    {
        "receipt_id": 3,
        "phone": "5555555555",
        "amount": "7589",
        "status": "Success",
        "receipt_item" : [
            {
              "receipt_id" : 3,
              "item_id" : 1,
              "quantity" : 23,
              "price" : 4355
            },
            {
              "receipt_id" : 3,
              "item_id" : 2,
              "quantity" : 41,
              "price" : 3665
            }

        ]
    }
]

先感谢您。

标签： mysqllaravel-5

解决方案

您可以使用JSON_ARRAYAGG和JSON_OBJECT函数来完成此操作。我认为这是你需要的：

SELECT JSON_ARRAYAGG(JSON_OBJECT('receipt_id', R.receipt_id, 'phone', R.phone, 'amount', R.amount, 'status', R.status, 'receipt_Item', I.Item))
from Receipts R
LEFT JOIN 
(SELECT receipt_id, JSON_ARRAYAGG(JSON_OBJECT('receipt_id', receipt_id, 'item_id', item_id, 'quantity', quantity, 'price', price))  AS Item from ReceiptItem Group BY receipt_id) I ON R.receipt_id = I.receipt_id

DBFiddler 示例可以在这里找到

推荐阅读