How does mysql query many-to-many relational tables?

Table relationship:

question:
how to query the data shown in the figure using native sql statements?
expected data structure (the list item of the product appears as a nested object):

{
  "category": [
    {
      "id": 1,
      "name": "name1",
      "product":[
          {
            "id":2,
            "detail":""
          },
          {
            "id":8,
            "detail":""
          }
      ]
    }
  ]
}

Mysql sql backend database

Dec.13,2021

there are two ways,

first query category , and then traverse category to find product of each category , which will lead to Number1 query problems
first query category , and then query all category.id to in , and match them in memory according to the relationship, as long as 2 times.

recommend the second kind, Chestnut:

take out the category list you want
```
select * from category
```

1 id is put into in joint queries category_product and product to get product list

select category_id, product.id, product.detail from category_product where category_id in (...)
left join product 
on category_product.product_id=product.id

put the product obtained in 2 into the corresponding category.product array in 1 according to category_id .
```
...
```

Previous: The problem of creating tables in JPA

Next: Is there any security risk when TOKEN is added to HEADER and HTTPS is used to communicate?