Query recursively nested structure

SQL++
1

Hello, everyone.

I have the following structures.
Menu (root object):

{
  "id": 1,
  "dishes": [{
    "dishId": 1,
    "amount": 5
  }]
}

Dish: (subobject)

{
  "id": 1,
  "nutrients": [{
    "nutId": 1,
    "amount": 4
  }],
  "products": [{
    "prodId": 1,
    "amount": 8
  }]
}

Nutrient: (subsubobject)

{
  "id": 1,
  ...
}

Product: (subsubobject)

{
  "id": 1,
  ...
}

I need to get a recursively nested structure like the following:

[{
  "id": 1,
  "dishes": [{
    "dishId": 1,
    "amount": 5,
    "dish": {
      "nutrients": [{
        "nutId": 1,
        "amount": 4,
        "nutrient": {...}
      }],
      "products": [{
        "prodId": 1,
        "amount": 8,
        "product": {...}
      }]
    }
  }]
 },
 ...
]

I am a newbie, so I’ve been able to manage only one level of nested objects. Something like that:

SELECT  MENU.*, dishes
FROM bucket AS MENU
UNNEST MENU.dishes AS DA
JOIN bucket AS DISH ON DISH.id = DA.dishId
GROUP BY MENU
LETTING dishes = ARRAY_AGG(OBJECT_PUT(DA, "dish", DISH))

I don’t have any idea how to get a deeper level of nested objects. I am currently using Community Edition 6.0.0 build 1693.

PS. Sorry for my English, it’s not my first language.

2

Assume your keys are dish:1, product:1 if not adjust accordingly

SELECT m.*, (SELECT dish.*, (SELECT dish.*,
                                    (SELECT n.*, nutrient
                                     FROM d.nutrients AS n
                                     LET nutrient = (SELECT RAW no FROM default AS no USE KEYS "nutrient:"|| TO_STRING(n.nutId))[0]
                                     ) AS nutrients,
                                    (SELECT p.*, product
                                     FROM d.products AS p
                                     LET product = (SELECT RAW po FROM default AS po USE KEYS "product:"|| TO_STRING(p.prodId))[0]
                                     ) AS products
                             FROM default AS d USE KEYS "dish:"|| TO_STRING(dish.dishId))
             FROM m.dishes AS dish) AS dishes
FROM default AS m
WHERE m.type = "menu";
1 Like
3

Work like a charm. Thanks a lot.