It looks like your address
column is formatted as a JSON document. MySQL 5.7 and later versions support JSON functions, so you can do the following query:
SELECT
JSON_UNQUOTE(JSON_EXTRACT(u.address, '$.city')) AS `city`,
JSON_UNQUOTE(JSON_EXTRACT(u.address, '$.first')) AS `first`,
JSON_UNQUOTE(JSON_EXTRACT(u.address, '$.state')) AS `state`,
JSON_UNQUOTE(JSON_EXTRACT(u.address, '$.second')) AS `second`,
JSON_UNQUOTE(JSON_EXTRACT(u.address, '$.country')) AS `country`,
JSON_UNQUOTE(JSON_EXTRACT(u.address, '$.zipcode')) AS `zipcode`
FROM ...
There's also a shorthand syntax:
SELECT
u.address->>'$.city' AS `city`,
...and similar for each field...
* Be the first to Make Comment