Separating out a DB field in SQL statement ?

• Yogesh Kumar
• 04-May-2022
• 0

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