MySQL nested Json Object using nodeJs

Question

I have two MySQL tables

category(id, name)
product(id, name, category_id)

product(category_id) is a foreign key to category(id), the relationship is one to many to one relationship so one category can have numerous products.

I am trying to get this from the database and return it in JSON format for multiple categories, so it would look like this:

 [
  {
    "id": 1,
    "name": "Category 1",
    "products": [
      {
        "id": 1,
        "name": "Chicken"
      },
      {
        "id": 2,
        "name": "Beef"
      }
    ]
  },
  {
    "id": 2,
    "name": "Category 2",
    "products": [
      {
        "id": 3,
        "item": "Fries"
      },
      {
        "id": 4,
        "item": "Burgers"
      }
    ]
  }
]

Where the products are nested within the entity of their corresponding category.

I am a newbie and not sure how i would reproduce this using nodeJs from MySQL. Kindly assist

GMB · Accepted Answer · 2019-11-04 20:59:24Z

In MySQL, you can use json generator function json_object() and json aggregate function json_arrayagg().

The following query produces one record per category, each holding a json object:

select 
    json_object(
        'id', c.id,
        'name', c.name,
        'products', json_arrayagg(
            json_object(
                'id', p.id,
                'name', p.name
            )
        )
    ) cat
from category c
inner join product p on p.category_id = c.id
group by c.id, c.name

If you want to aggregate all records in a unique json array, then you can add another level of aggregation:

select json_arrayagg(cat)
from (
    select 
        json_object(
            'id', c.id,
            'name', c.name,
            'products', json_arrayagg(
                json_object(
                    'id', p.id,
                    'name', p.name
                )
            )
        ) cat
    from category c
    inner join product p on p.category_id = c.id
    group by c.id, c.name   
) t

Collectives™ on Stack Overflow

MySQL nested Json Object using nodeJs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related