How to do nested select [example json provided]

[anonimusprogramus]

A shop containing its categories with all items, how to get that in surrealdb?

Thank you

Here's the db:

shop: [
  {
    id: 'shop:a',
    name: 'This Shop A'
  },
  {
    id: 'shop:b',
    name: 'That Shop B'
  }
]

categories: [
  {
    id: 'category:food',
    name: 'Food',
    shopId: 'shop:a'
  },
  {
    id: 'category:beverages',
    name: 'Beverages',
    shopId: 'shop:a'
  },
  {
    id: 'category:promo',
    name: 'Promotional',
    shopId: 'shop:a'
  },
  {
    id: 'category:vegetables',
    name: 'Vegetables',
    shopId: 'shop:b'
  }
]

items: [
  {
    id: 'item:burger',
    name: 'Beef Burger',
    price: 3,
    categories: ['category:food'],
    shopId: 'shop:a'
  },
  {
    id: 'item:rollups',
    name: 'Chicken Roll Ups',
    price: 2,
    categories: ['category:food', 'category:promo'],
    shopId: 'shop:a'
  },
  {
    id: 'item:water',
    name: 'Mineral Water',
    price: 1,
    categories: ['category:beverages'],
    shopId: 'shop:a'
  },
  {
    id: 'item:soda',
    name: 'Pure Cola',
    price: 1,
    categories: ['category:beverages'],
    shopId: 'shop:a'
  },
  {
    id: 'item:tea',
    name: 'Thai Tea',
    price: 1,
    categories: ['category:beverages', 'category:promo'],
    shopId: 'shop:a'
  }
]

Expected result would be:

result: {
  id: 'shop:a',
  name: 'This Shop A',
  categories: [
    {
      id: 'category:food',
      name: 'Food',
      items: [
        {
          id: 'item:burger',
          name: 'Beef Burger',
          price: 3
        },
        {
          id: 'item:rollups',
          name: 'Chicken Roll Ups',
          price: 2
        }
      ]
    },
    {
      id: 'category:beverages',
      name: 'Beverages',
      items: [
        {
          id: 'item:water',
          name: 'Mineral Water',
          price: 1
        },
        {
          id: 'item:soda',
          name: 'Pure Cola',
          price: 1
        },
        {
          id: 'item:tea',
          name: 'Thai Tea',
          price: 1
        }
      ]
    },
    {
      id: 'category:promo',
      name: 'Promotional',
      items: [
        {
          id: 'item:rollups',
          name: 'Chicken Roll Ups',
          price: 2
        },
        {
          id: 'item:tea',
          name: 'Thai Tea',
          price: 1
        }
      ]
    }
  ]
}

[anonimusprogramus]

After reading docs throuhly, I find that it's best for me to have a mindset switch from JOIN to edge, hopefully then I can get best of surrealdb.

So I consider my question about nested select has been answered, time to learn new things.