Separating out a DB field in SQL statement-CodePudding

Is there a way to use a SQL statement to separate out fields in a DB?

For instance, using u.address in a SQL statement returns {"city": "Fullerton", "first": "369 Acacia Ave", "state": "California", "second": "", "country": "United States", "zipcode": "92831"}

I want each of those address fields in a separate column in Excel. I apologize if this is easy. I'm not well versed in SQL.

CodePudding user response：

You can do it after you get what was returned from the database.

x={"city": "Fullerton", "first": "369 Acacia Ave", "state": "California", "second": "", "country": "United States", "zipcode": "92831"}

print(x["city"])

CodePudding user response：

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...

See https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-extract

To be honest, in almost every case where I see JSON used in a MySQL database, it would have been better to use normal columns.

Here's a demo:

https://www.db-fiddle.com/f/24U6g9FeHqZSfWtgKFKNkG/0