json_decode on result from mysql json_object not working

Question

I'm trying to do a query on a table where I want to select some columns and some other columns as a nested json object, for example, I want to select the list of users with it's address as a nested object.

SELECT
    u.first_name, u.last_name, u.age, u.phone,
    JSON_OBJECT(
        'zip', a.zip,
        'address', a.address,
        'number', a.number,
        'city', a.city,
        'state', a.state
    ) as 'address'

FROM
    tb_user u,
    tb_user_address a

WHERE u.id_address = a.id

The expected result would be something like this:

[
    {
        first_name: 'my name',
        last_name: 'my last name',
        ... // Some other fields
        address: {
            zip: '987',
            address: 'street name',
            ... // Some other fields
        }
    },
    {
        // Another user
    }
]

But when it returns, the address field is just a plain string, like this: address: "{'zip': '987', 'address': 'street name', 'city': 'cityname', 'state'...};" and only this field (the one using JSON_OBJECT) is returning like this, the others are ok.

When I return the result from the sql query, I'm doing like this:

$user_list = json_decode(json_encode($query_result), true);

But it also doesn't resolve the problem. Is there a way to turn that string into a valid json object?

SELECT JSON_OBJECT(...) as `address` FROM ... causes the fields inside the JSON_OBJECT call to be converted to a JSON string and returned as the address column. Your first name etc. aren't part of the JSON_OBJECT call and as such won't be in there. — Kep
– Kep, Commented Apr 2, 2018 at 19:22
address field is just a plain string That's JSON, what did you think JSON was? — AbraCadaver
– AbraCadaver, Commented Apr 2, 2018 at 19:23
The MySQL driver has no idea what JSON is so you're left decoding it like that. If you use an ORM it can do that for you transparently, but otherwise you can't expect this to happen by magic alone. — tadman
– tadman, Commented Apr 2, 2018 at 19:24
I haven't used JSON_OBJECT before, but you can just wrap the whole thing up in a json_object so you can json_decode the entire row that is returned: SELECT JSON_OBJECT(first_name, last_name, age, phone, JSON_OBJECT(<address fields)) FROM your table... Then json_decode($query_result) — JNevill
– JNevill, Commented Apr 2, 2018 at 19:47

Cobra_Fast · Accepted Answer · 2018-04-02 19:25:00Z

1

You're creating the address JSON object in MySQL, so it gets returned as a string containing the JSON formatted data among the other columns.

You could do the following to convert it to a PHP array before JSON-encoding the entire result:

$user_list = array_map(
    function ($v) { return json_decode($v['address'], true); },
    $query_result
);
$user_list = json_encode($user_list);

answered Apr 2, 2018 at 19:25

Cobra_Fast

16.2k8 gold badges64 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

json_decode on result from mysql json_object not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related