如何从SQL数据库构建嵌套JSON数组？

Question

Here is the JSON format I got from a SQL database:

{
    "xData": ["2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2005-05", "2007-11"]
} {
    "name": "Female",
    "data": [0.915, 0.239, 0.1944, 0.688, 0.8483, 0.0466, 0.3326, 0.6736, 0.0361, 0.4999, 0.8163, -0.3561, 1.1272, 0.5887, -0.1083, 0.713, -0.3287, 0.377, -0.0028, 0.6898, 0.734, 0.3036, -0.1518, 0.4427, 0.4422, 0.308, 0.7586, -0.1638, 0.6759, 0.4066, 0.1616, 0.6232, -0.8194, 0.2482, -0.2033, 0.2565, 0.3171, -0.0436, 0.2752, 0.4565, 0.5665, 0.4314, 0.4077, 0.4002, 0.9626, 0.9249]
} {
    "name": "Male",
    "data": [0.1418, 1.2012, 0.3303, 0.2868, 0.3277, 0.2204, 0.6071, 0.4635, 0.7447, 0.3744, 0.3539, 0.9404, 0.897, 0.1206, 0.2797, 0.7477, 0.2737, 0.8536, 0.924, 1.0244, 0.0613, -0.3384, 0.6353, -0.5389, 0.773, 0.7062, 0.174, 0.2941, 0.9353, 0.7985, 1.4301, 0.2805, 0.9922, 0.9169, 0.6853, 0.4048, 0.0789, 0.4576, 1.0924, 0.8866, 0.3695, -0.1778, 0.4321, 0.4068, 0.8123, 0.9536, 0.4083, 0.6146, 0.088, 0.4721, 0.4143, 0.4272, 0.7681, -0.1504]
}

But what I want to build is like a nest format and add one name

{
    "xData": ["2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2005-05", "2007-11"],
    "datasets": [{
        "name": "Female",
        "data": [0.915, 0.239, 0.1944, 0.688, 0.8483, 0.0466, 0.3326, 0.6736, 0.0361, 0.4999, 0.8163, -0.3561, 1.1272, 0.5887, -0.1083, 0.713, -0.3287, 0.377, -0.0028, 0.6898, 0.734, 0.3036, -0.1518, 0.4427, 0.4422, 0.308, 0.7586, -0.1638, 0.6759, 0.4066, 0.1616, 0.6232, -0.8194, 0.2482, -0.2033, 0.2565, 0.3171, -0.0436, 0.2752, 0.4565, 0.5665, 0.4314, 0.4077, 0.4002, 0.9626, 0.9249],
        "type": "line",
    }, {
        "name": "Male",
        "data": [0.1418, 1.2012, 0.3303, 0.2868, 0.3277, 0.2204, 0.6071, 0.4635, 0.7447, 0.3744, 0.3539, 0.9404, 0.897, 0.1206, 0.2797, 0.7477, 0.2737, 0.8536, 0.924, 1.0244, 0.0613, -0.3384, 0.6353, -0.5389, 0.773, 0.7062, 0.174, 0.2941, 0.9353, 0.7985, 1.4301, 0.2805, 0.9922, 0.9169, 0.6853, 0.4048, 0.0789, 0.4576, 1.0924, 0.8866, 0.3695, -0.1778, 0.4321, 0.4068, 0.8123, 0.9536, 0.4083, 0.6146, 0.088, 0.4721, 0.4143, 0.4272, 0.7681, -0.1504],
        "type": "line",
    }]
}

Thank you so much!

Answer 1

<?php
$data = <<<_TEXT
{
    "xData": ["2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2004-04", "2005-05", "2007-11"]
} {
    "name": "Female",
    "data": [0.915, 0.239, 0.1944, 0.688, 0.8483, 0.0466, 0.3326, 0.6736, 0.0361, 0.4999, 0.8163, -0.3561, 1.1272, 0.5887, -0.1083, 0.713, -0.3287, 0.377, -0.0028, 0.6898, 0.734, 0.3036, -0.1518, 0.4427, 0.4422, 0.308, 0.7586, -0.1638, 0.6759, 0.4066, 0.1616, 0.6232, -0.8194, 0.2482, -0.2033, 0.2565, 0.3171, -0.0436, 0.2752, 0.4565, 0.5665, 0.4314, 0.4077, 0.4002, 0.9626, 0.9249]
} {
    "name": "Male",
    "data": [0.1418, 1.2012, 0.3303, 0.2868, 0.3277, 0.2204, 0.6071, 0.4635, 0.7447, 0.3744, 0.3539, 0.9404, 0.897, 0.1206, 0.2797, 0.7477, 0.2737, 0.8536, 0.924, 1.0244, 0.0613, -0.3384, 0.6353, -0.5389, 0.773, 0.7062, 0.174, 0.2941, 0.9353, 0.7985, 1.4301, 0.2805, 0.9922, 0.9169, 0.6853, 0.4048, 0.0789, 0.4576, 1.0924, 0.8866, 0.3695, -0.1778, 0.4321, 0.4068, 0.8123, 0.9536, 0.4083, 0.6146, 0.088, 0.4721, 0.4143, 0.4272, 0.7681, -0.1504]
}
_TEXT;

$datasets = [];
$json = "";

// The data above is NO valid json.
// So we help us splitting at the closing brackets.

$data = explode("} ", $data);
$i = 0;
foreach($data as $part)
{
    // Splitting removes the last closed bracket.
    // But to reparse it as valid JSON we add it, if it is not there.
    if(substr($part, -1, 1) !== "}") $part .= "}";

    if(!$i)
    {
        // first element
        // decode and create a PHP array out of it
        $json = json_decode($part);
    }
    else
    {
        // all other elements
        // decode to a local variable
        $dataset = json_decode($part);

        // add the line type
        $dataset->type = "line";

        // store it into an array to recompose the JSON afterwards.
        $datasets[] = $dataset;
    }
    $i++;
}

// Add the datasets to the previously created PHP array.
$json->datasets = $datasets;

// Encode the PHP array to get the JSON.
$finalJson = json_encode($json);

echo $finalJson;