MySQL一对多到JSON格式 [英] MySQL One-to-Many to JSON format
问题描述
我有两个MySQL表:
User (id, name)
Sale (id, user, item)
Sale(user)是User(id)的外键，因此这是一对多关系(一个用户可以进行很多销售).
我正在尝试从数据库中获取此信息，并以JSON格式返回给多个用户，所以它看起来像这样:
[
{
"id": 1,
"name": "User 1",
"sales": [
{
"id": 1,
"item": "t-shirt"
},
{
"id": 2,
"item": "jeans"
}
]
},
{
"id": 2,
"name": "User 2",
"sales": [
{
"id": 3,
"item": "sweatpants"
},
{
"id": 4,
"item": "gloves"
}
]
}
]
销售"实体嵌套在其相应用户"的实体中.
问题是，从数据库中查询并将其转换为JSON的最佳方法是什么?我可以对所有用户运行查询，然后遍历每个用户并运行查询以获取其销售量，但这非常慢.或者，我可以在用户和销售人员之间进行外部联接，然后将其解析为JSON格式的代码，但这会从数据库发送多余的信息(包括每笔销售的整套用户数据集)，并需要在其中循环遍历所有信息.代码.有方便的方法吗?顺便说一下，我正在使用Python 3.7.
这是一个可能满足您要求的SQL查询.它使用 DB Fiddle上的演示
如果用JSON_PRETTY包装返回值，则输出如下:
[
{
"id": 1,
"name": "User 1",
"sales": [
{
"id": 1,
"item": "t-shirt"
},
{
"id": 2,
"item": "jeans"
}
]
},
{
"id": 2,
"name": "User 2",
"sales": [
{
"id": 3,
"item": "sweatpants"
},
{
"id": 4,
"item": "gloves"
}
]
}
]
编辑:这是MySQL的(难看)解决方案< 5.7，其中不提供JSON支持.它仅依赖于字符串操作函数.请注意，这仅在varchar字段不包含"字符时有效:
SELECT
CONCAT(
'[',
GROUP_CONCAT( CONCAT( '{ "id":', u.id, ', "name":"', u.name, '", "sales":', s.sales, ' }' ) SEPARATOR ', ' ),
']'
)
FROM
user u
LEFT JOIN (
SELECT
user,
CONCAT(
'[',
GROUP_CONCAT( CONCAT( '{ "id":', id, ', "item":"', item, '" }' ) SEPARATOR ', '),
']'
) sales
FROM sale
GROUP BY user ) s ON s.user = u.id
I have two MySQL tables:
User (id, name)
Sale (id, user, item)
Where Sale(user) is a foreign key to User(id), so this is a one-to-many relationship (one user can make many sales).
I'm trying to get this from the database and return it in JSON format for multiple users, so it would look like this:
[
{
"id": 1,
"name": "User 1",
"sales": [
{
"id": 1,
"item": "t-shirt"
},
{
"id": 2,
"item": "jeans"
}
]
},
{
"id": 2,
"name": "User 2",
"sales": [
{
"id": 3,
"item": "sweatpants"
},
{
"id": 4,
"item": "gloves"
}
]
}
]
Where the "sales" entities are nested within the entity of their corresponding "user".
So the question is, what is the best way to query this from the DB and turn it into JSON? I could run a query for all Users, then iterate through each one and run a query to get their sales, but this is quite slow. Or, I could do an outer join between Users and Sales and then parse that in code into the JSON format, but this sends excess information from the DB (includes the entire set of User data for each Sale) and requires looping through it all in code. Is there a convenient way to do this? I'm using Python 3.7, by the way.
Here is a SQL query that might meet your requirement.It uses MySQL JSON_ARRAYAGG() aggregate function to generate an array of JSON objects (which are created using JSON_OBJECT()).
An intermediate level of grouping is performed within the join, to generate the sales JSON array of each user. Then the results are aggregated into a single line, with one column that contains the resulting JSON array of objects.
SELECT
JSON_ARRAYAGG(JSON_OBJECT('id', u.id, 'name', u.name, 'sales', s.sales))
FROM
user u
LEFT JOIN (
SELECT
user,
JSON_ARRAYAGG(JSON_OBJECT('id', id, 'item', item)) sales
FROM sale
GROUP BY user
) s ON s.user = u.id
If you wrap the return value with JSON_PRETTY, the output is as follows :
[
{
"id": 1,
"name": "User 1",
"sales": [
{
"id": 1,
"item": "t-shirt"
},
{
"id": 2,
"item": "jeans"
}
]
},
{
"id": 2,
"name": "User 2",
"sales": [
{
"id": 3,
"item": "sweatpants"
},
{
"id": 4,
"item": "gloves"
}
]
}
]
Edit : here is an (ugly) solution for MySQL < 5.7, where JSON support is not available. It relies only on string manipulation functions. Please note that this will only work as long as the varchar fields do not contain the " character :
SELECT
CONCAT(
'[',
GROUP_CONCAT( CONCAT( '{ "id":', u.id, ', "name":"', u.name, '", "sales":', s.sales, ' }' ) SEPARATOR ', ' ),
']'
)
FROM
user u
LEFT JOIN (
SELECT
user,
CONCAT(
'[',
GROUP_CONCAT( CONCAT( '{ "id":', id, ', "item":"', item, '" }' ) SEPARATOR ', '),
']'
) sales
FROM sale
GROUP BY user ) s ON s.user = u.id
这篇关于MySQL一对多到JSON格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
