mysql的json格式_MySQL一对多到JSON格式

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