使用PHP和MySQL创建多级JSON [英] Create multi-level JSON with PHP and MySQL

问题描述

我不知道如何使用PHP和MySQL创建多级JSON输出.

I can't figure out how to create a multi-level JSON output using PHP and MySQL.

我有这个查询(此问题的简化版)

I have this query (simplified for this question)

$query = "SELECT 
           1 as id, 
           JSON_OBJECT('key1', 1, 'key2', 'abc') as json1";

我变成一个数组

while ($row = mysqli_fetch_assoc($result)) {
        $resultArray[] = $row;
}
return $resultArray;

，然后使用json_encode:

and then use json_encode:

echo json_encode($result);

这让我

[
    {
        id: "1",
        json1: "{"key1": 1, "key2": "abc"}"
    }
]

，即变量以字符串形式返回.

i.e., the variable is returned as a string.

我想要实现的是将变量json_test作为JSON的第二级，例如:

What I would like to achieve is to have the variable json_test as a second level of JSON, like so:

[
    {
        id: "1",
        json1: 
                  {
                       key1: 1, 
                       key2: "abc"
                  }
    }
]

我尝试过以下有关本网站的提示，但并不高兴:

I have tried following hints around this site, but no joy:

JSON_ARRAY(GROUP_CONCAT(JSON_OBJECT('key1', 1, 'key2', 'abc'))) AS json2

给我

json2: "["{\"key1\": 1, \"key2\": \"abc\"}"]",

和

CAST(CONCAT('[',GROUP_CONCAT(JSON_OBJECT('key1', 1, 'key2', 'abc')),']') AS JSON) AS json3

给我

json3: "[{"key1": 1, "key2": "abc"}]"

任何提示都将不胜感激.

Any hints are greatly appreciated.

我使用的是PHP 7.0.25和MySQL 5.7.20.

I'm on PHP 7.0.25 and MySQL 5.7.20.

推荐答案

JSON_OBJECT作为字符串返回给PHP(如 prodigitalson 评论)

The JSON_OBJECT is returned to PHP as a string (as prodigitalson commented)

您希望将所有数据作为关联数组.

You want to have all your data as an associative array.

为此，在您发布的示例中，必须通过json_decode传递json1.

In order to do that, in the example you posted, json1 must be passed thru json_decode.

while ($row = mysqli_fetch_assoc($result)) {
        $row['json1'] = json_decode( $row['json1'], true ); // <----
        $resultArray[] = $row;
}
return $resultArray;

现在您应该获得理想的结果:

Now you should get the desired result:

echo json_encode($resultArray);

这篇关于使用PHP和MySQL创建多级JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！