如何重新格式化从PostgreSQL通过PHP获取的分层JSON数据

Got it, let's tackle turning that flat JSON array from your PostgreSQL query into a properly nested hierarchical structure using PHP. Here's a step-by-step approach tailored to your data format:

Step 1: Prepare Your Raw Data #

First, make sure you have your JSON converted into a PHP associative array. If you're starting directly from the JSON string you got from the database, use json_decode with the second parameter set to true:

// Your raw JSON string from PostgreSQL
$rawJson = '[{"treelevel":"1","app":"Front","lrflag":null,"ic":null,"price":null,"parentlevel":"0","seq":"27", "indexlistid":439755}, {"treelevel":"2","app":"V-Series","lrflag":null,"ic":null,"price":null,"parentlevel":"1","seq":"28", "indexlistid":439755}, {"treelevel":"3","app":"opt J56","lrflag":null,"ic":null,"price":null,"parentlevel":"2","seq":"29", "indexlistid":439755}, {"treelevel":"4","app":"R.","lrflag":"R","ic":null,"price":null,"parentlevel":"3","seq":"30", "indexlistid":439755}]';

// Convert JSON to a PHP associative array
$flatNodes = json_decode($rawJson, true);

Step 2: Build a Node Map & Identify Root Nodes #

We'll create a quick lookup map to find parent nodes by their treelevel, and separate out the root nodes (those with parentlevel="0"):

$nodeMap = [];
$rootNodes = [];

foreach ($flatNodes as $node) {
    // Add an empty `children` array to each node to store its sub-nodes
    $node['children'] = [];
    // Map each node by its treelevel for fast lookup
    $nodeMap[$node['treelevel']] = $node;
    
    // Collect root nodes (parentlevel is 0)
    if ($node['parentlevel'] === '0') {
        $rootNodes[] = $node;
    }
}

Step 3: Nest Child Nodes Under Their Parents #

Now iterate through all nodes again, and attach each node to its parent's children array using our lookup map:

foreach ($flatNodes as $node) {
    $parentLevel = $node['parentlevel'];
    // Check if the parent exists in our map (skip orphaned nodes if any)
    if (isset($nodeMap[$parentLevel])) {
        $nodeMap[$parentLevel]['children'][] = $node;
    }
}

Step 4: Get Your Nested Structure #

The $rootNodes variable now holds your fully nested hierarchical data. You can convert it back to pretty-printed JSON if needed:

$nestedJson = json_encode($rootNodes, JSON_PRETTY_PRINT);
echo $nestedJson;

What the Output Looks Like #

Your final nested JSON will look something like this:

[
    {
        "treelevel": "1",
        "app": "Front",
        "lrflag": null,
        "ic": null,
        "price": null,
        "parentlevel": "0",
        "seq": "27",
        "indexlistid": 439755,
        "children": [
            {
                "treelevel": "2",
                "app": "V-Series",
                "lrflag": null,
                "ic": null,
                "price": null,
                "parentlevel": "1",
                "seq": "28",
                "indexlistid": 439755,
                "children": [
                    {
                        "treelevel": "3",
                        "app": "opt J56",
                        "lrflag": null,
                        "ic": null,
                        "price": null,
                        "parentlevel": "2",
                        "seq": "29",
                        "indexlistid": 439755,
                        "children": [
                            {
                                "treelevel": "4",
                                "app": "R.",
                                "lrflag": "R",
                                "ic": null,
                                "price": null,
                                "parentlevel": "3",
                                "seq": "30",
                                "indexlistid": 439755,
                                "children": []
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

Key Notes #

• This approach runs in O(n) time (we only loop through the flat array twice), so it's efficient even for large datasets.
• If you have orphaned nodes (nodes whose parentlevel doesn't match any treelevel), they'll be ignored—you can add a check to handle these if needed.
• Make sure to use strict equality (===) when comparing parentlevel values since they're stored as strings in your data.

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