mysql json多级,使用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";

which I turn into an array

while ($row = mysqli_fetch_assoc($result)) {

$resultArray[] = $row;

}

return $resultArray;

and then use json_encode:

echo json_encode($result);

This gets me

[

{

id: "1",

json1: "{"key1": 1, "key2": "abc"}"

}

]

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

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

gives me

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

and

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

gives me

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

Any hints are greatly appreciated.

I'm on PHP 7.0.25 and MySQL 5.7.20.

解决方案

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

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

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);