alexa
Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry

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

326 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