alexa

Separating out a DB field in SQL statement ?

Separating out a DB field in SQL statement ?

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

415 0
7

Write a Comments


* Be the first to Make Comment

GoodFirms Badge
GoodFirms Badge

Fix Your Meeting With Our SEO Consultants in India To Grow Your Business Online

Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry