如何使自定义json对象的结果组by子句，php - How to make custom json object with the result of a group by clause, php

How to make custom json object with the result of a group by clause, php

我试图从php脚本获得json对象。到目前为止，我已经做到了。

<?php
$connection=pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=root")  or die("Can't connect to database".pg_last_error());
$result = pg_query('SELECT * FROM playground'); 
$rows = array();
while($r = pg_fetch_assoc($result)) {
 $rows[] = $r;
}
print json_encode(array_values(pg_fetch_all($result)));
?>

返回以下对象

[{"name":"slide","color":"blue","location":"south"},{"name":"slide","color":"green","location":"north"},{"name":"dont","color":"red","location":"west"}]

然而，我试图将它们存储在....

[ 
{"name":"slide","values": [ "color" : ["blue","green"], "location": ["south", "north"] ]},
{"name":"dont","values" : ["color" : ["red"],"location" : ["west"] } 
]

基本上，我想通过父字段值作为键分组公共组，并将其值作为json对象的值。

但是我不能。如果有人能帮忙，我将不胜感激。谢谢。

您可以直接遍历$result并自己创建所需的数组结构:

while($r = pg_fetch_assoc($result)) {
    if (!array_key_exists($r['name'], $rows)) {
        $rows[$r['name']] = array('values' => array( 'color' => array(), 'location' => array() ));
    }
    if (!in_array($r['color'], $rows[$r['name']]['values']['color'])) {
        $rows[$r['name']]['values']['color'][] = $r['color'];
    }
    if (!in_array($r['location'], $rows[$r['name']]['values']['location'])) {
        $rows[$r['name']]['values']['location'][] = $r['location'];
    }
}
echo json_encode($rows);

这将以的形式生成JSON。

{
    "slide": {
        "values": {
            "color": [
                "blue",
                "green"
            ],
            "location": [
                "south",
                "north"
            ]
        }
    },
    "dont": {
        "values": {
            "color": [
                "red"
            ],
            "location": [
                "west"
            ]
        }
    }
}