Single sequel query with joins to combine multiple rows from other tables that have the same ID-CodePudding

I'm trying to use a single query to get information from 4 different tables and combine them, as some tables may have multiple rows where the ID matches it's relational ID.

I have 4 tables:

parents
children - references parent ID
prices - references child ID
attributes - references parent ID

Parents Table

id	name
1	Product Name

Children Table

child_id	parent_id
1	1
2	1

Prices Table

child_id	price
1	1.99
1	6.99
2	1.49

Attributes Table

child_id	label	value
1	Colour	Red
1	Colour	Blue
1	Size	Large

Here's the first part, half what I want. But how do I append the prices and attributes to the children?

const query = 'SELECT parents.id, parents.title, JSON_ARRAYAGG(children.append) AS children \
      FROM parents \
      LEFT JOIN children ON (parents.id = children.parent_id) \
      GROUP BY parents.id';

I want to end up with some object like:

    [
  {
    "parent_id": 1,
    "title": "Product Name",
    "attributes": [
      {
        "label": "Colour",
        "value": "Red"
      },
      {
        "label": "Colour",
        "value": "Blue"
      },
      {
        "label": "Size",
        "value": "Large"
      }
    ],
    "children": [
      {
        "child_id": 1,
        "prices": [
          {
            "price": 1.99
          },
          {
            "price": 6.99
          }
        ]
      },
      {
        "child_id": 2,
        "prices": [
          {
            "price": 1.49
          }
        ]
      }
    ]
  }
]

CodePudding user response：

You need to generate your sub-json first using JSON_OBJECT JSON_ARRAY_AGG, for your attributes, your prices, your children. Once you have all of them, you can apply the higher-level aggregation.

WITH json_attr AS (
    SELECT child_id, 
           JSON_ARRAYAGG(JSON_OBJECT('label', label, 
                                     'value', value_)) AS json_attr
    FROM attributes
    GROUP BY child_id
), json_pric AS (
    SELECT child_id, 
           JSON_ARRAYAGG(JSON_OBJECT('price', ROUND(price,2))) AS json_pric
    FROM prices
    GROUP BY child_id
), json_child AS (
    SELECT JSON_ARRAYAGG(JSON_OBJECT('child_id', child_id ,
                                     'prices'  , json_pric)) AS json_child
    FROM json_pric
)
SELECT JSON_ARRAYAGG(JSON_OBJECT('parent_id' ,          p.id,
                                 'title'     ,        p.name,
                                 'attributes',  aj.json_attr,
                                 'children'    , cj.json_child )) AS your_json
FROM       parents     p
INNER JOIN children    c ON p.id = c.parent_id
INNER JOIN json_attr  aj ON c.child_id = aj.child_id
CROSS JOIN json_child cj
GROUP BY p.id

Check the demo here.

In MySQL 5.7, you need to nest subqueries in place of common table expressions:


SELECT JSON_ARRAYAGG(JSON_OBJECT('parent_id' ,          p.id,
                                 'title'     ,        p.name,
                                 'attributes',  aj.json_attr,
                                 'children'    , cj.json_child )) AS your_json
FROM       parents     p
INNER JOIN children    c ON p.id = c.parent_id
INNER JOIN (SELECT child_id, 
                   JSON_ARRAYAGG(JSON_OBJECT('label', label, 
                                             'value', value_)) AS json_attr
            FROM attributes
            GROUP BY child_id)  aj ON c.child_id = aj.child_id
CROSS JOIN (SELECT JSON_ARRAYAGG(JSON_OBJECT('child_id', child_id ,
                                             'prices'  , json_pric)) AS json_child
            FROM (SELECT child_id, 
                         JSON_ARRAYAGG(JSON_OBJECT('price', ROUND(price,2))) AS json_pric
                  FROM prices
                  GROUP BY child_id) json_pric) cj
GROUP BY p.id

Check the demo here.