Create multi-level JSON with PHP and MySQL

Question

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.

Paolo · Accepted Answer · 2017-12-27 23:10:00Z

1

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

edited Dec 27, 2017 at 23:10

answered Dec 27, 2017 at 21:29

Paolo

15.9k28 gold badges73 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sorak · Accepted Answer · 2017-12-27 18:40:21Z

0

Building the desired structure with MySQL can be done like this:

mysql> SELECT JSON_OBJECT('id', 1, 'json1', JSON_OBJECT('key1', 1, 'key2', 'abc')) AS obj;
+------------------------------------------------+
| obj                                            |
+------------------------------------------------+
| {"id": 1, "json1": {"key1": 1, "key2": "abc"}} |
+------------------------------------------------+
1 row in set (0.00 sec)

And in PHP, we can either go from JSON to array:

php > var_dump(json_decode('{"id":1,"json1":{"key1":1,"key2":"abc"}}', TRUE));
array(2) {
  ["id"]=>
  int(1)
  ["json1"]=>
  array(2) {
    ["key1"]=>
    int(1)
    ["key2"]=>
    string(3) "abc"
  }
}

Or array to JSON:

php > $tmp = ['id' => 1, 'json1' => ['key1' => 1, 'key2' => 'abc']];
php > var_dump($tmp);
array(2) {
  ["id"]=>
  int(1)
  ["json1"]=>
  array(2) {
    ["key1"]=>
    int(1)
    ["key2"]=>
    string(3) "abc"
  }
}
php > print(json_encode($tmp));
{"id":1,"json1":{"key1":1,"key2":"abc"}}

answered Dec 27, 2017 at 18:40

sorak

2,6332 gold badges18 silver badges24 bronze badges

7 Comments

Sebastian Over a year ago

Thanks for your answer! Unfortunately, your suggestion doesn't seem to work - this is what I get: [ { obj: "{"id": 1, "json1": {"key1": 1, "key2": "abc"}}" } ]

sorak Over a year ago

As you can see, I copied and pasted directly from command-line PHP and MySQL. What are your needs that are not yet met?

sorak Over a year ago

What are you running, and what are the desired results?

sorak Over a year ago

You're currently putting in in an object as a string. If you use json_decode to make it into an object, then you can put it in as a key and re-encode the whole thing. If you supply your code I can show you how this would look

prodigitalson Over a year ago

mysqli will not cast a JSON_OBJECT to a php array or stdObject. you need to call json_decode in your loop if you want to re-encode for output.

|

Collectives™ on Stack Overflow

Create multi-level JSON with PHP and MySQL

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related