递归SELECT查询结果JSON优化：去除无子类分类的重复嵌套

Problem Breakdown #

You’re pulling menu data from a database that returns a nested JSON structure, but parentless categories (like Colazione with parent_id = NULL) are incorrectly wrapped in a duplicate subcategory. Your goal is to flatten these cases so the items array sits directly under the parent category, while keeping the nested structure intact for categories that have actual subcategories (like Pranzo with Primi piatti).

Original Problematic JSON:

{
  "menu": [
    { "Colazione": { "items": { "Colazione": [ { "item_name": "Cornetto" } ] } } },
    { "Pranzo": { "items": { "Primi piatti": [ { "item_name": "Pasta al sugo" }, { "item_name": "Pasta al sugo n.2" }, { "item_name": "Pasta al sugo n.3" } ] } } }
  ]
}

Desired Optimized JSON:

{
  "menu": [
    { "Colazione": { "items": [ { "item_name": "Cornetto" } ] } },
    { "Pranzo": { "items": { "Primi piatti": [ { "item_name": "Pasta al sugo" }, { "item_name": "Pasta al sugo n.2" }, { "item_name": "Pasta al sugo n.3" } ] } } }
  ]
}

Solution (PHP Implementation) #

Since you’re working with PHP (from your tooling context), here’s a function that processes the original menu array to fix the structure:

function optimizeMenuStructure($menu) {
    $optimizedMenu = [];
    
    foreach ($menu as $categoryEntry) {
        foreach ($categoryEntry as $categoryName => $categoryData) {
            // Check if items has a single key matching the category name (the unnecessary nest)
            if (is_array($categoryData['items']) && count($categoryData['items']) === 1 && array_key_exists($categoryName, $categoryData['items'])) {
                // Replace the nested subcategory with direct items array
                $optimizedEntry = [
                    $categoryName => [
                        'items' => $categoryData['items'][$categoryName]
                    ]
                ];
            } else {
                // Keep original structure for categories with actual subcategories
                $optimizedEntry = $categoryEntry;
            }
            
            $optimizedMenu[] = $optimizedEntry;
        }
    }
    
    return ['menu' => $optimizedMenu];
}

// Example usage with your original data
$originalMenu = [
    "menu" => [
        [ "Colazione" => [ "items" => [ "Colazione" => [ [ "item_name" => "Cornetto" ] ] ] ] ],
        [ "Pranzo" => [ "items" => [ "Primi piatti" => [ [ "item_name" => "Pasta al sugo" ], [ "item_name" => "Pasta al sugo n.2" ], [ "item_name" => "Pasta al sugo n.3" ] ] ] ] ]
    ]
];

$optimizedResult = optimizeMenuStructure($originalMenu['menu']);
echo json_encode($optimizedResult, JSON_PRETTY_PRINT);

How It Works #

1. Iterate over each category entry in the menu
2. For each category, check if the items property contains a single key that exactly matches the category name (the red flag for unnecessary nesting)
3. If the condition is met, replace the nested subcategory with the direct items array
4. Leave the original structure untouched for categories that have actual subcategories (like Pranzo)

Alternative: Client-Side JavaScript Solution #

If you prefer to handle this on the frontend, here’s a JavaScript version:

function optimizeMenuStructure(menu) {
    return menu.map(categoryEntry => {
        const categoryName = Object.keys(categoryEntry)[0];
        const categoryData = categoryEntry[categoryName];
        
        // Check for the unnecessary nested subcategory pattern
        if (typeof categoryData.items === 'object' && 
            Object.keys(categoryData.items).length === 1 && 
            Object.keys(categoryData.items)[0] === categoryName) {
            return {
                [categoryName]: {
                    items: categoryData.items[categoryName]
                }
            };
        }
        
        return categoryEntry;
    });
}

// Example usage
const originalMenu = [
    { "Colazione": { "items": { "Colazione": [ { "item_name": "Cornetto" } ] } } },
    { "Pranzo": { "items": { "Primi piatti": [ { "item_name": "Pasta al sugo" }, { "item_name": "Pasta al sugo n.2" }, { "item_name": "Pasta al sugo n.3" } ] } } }
];

const optimizedMenu = optimizeMenuStructure(originalMenu);
console.log(JSON.stringify({ menu: optimizedMenu }, null, 2));

Both solutions will output your desired JSON structure, correctly handling parentless categories while preserving nested subcategories where needed.

内容的提问来源于stack exchange，提问作者Michele Zotti