I need to run a query that produces a list of members and their postal addresses.  When I look at the nexus_customer_addresses, the address column is formatted like this:

{"lat":null,"long":null,"addressLines":["17 N. State Street 19th Floor"],"city":"Chicago","region":"Illinois","country":"US","postalCode":60602}

How can I extract the pieces of information, such as address, city, state,and zipcode, and put them into separate columns when I run my query?

 

Assuming you are accessing the database directly via MySQL, you could use the JSON_EXTRACT function (MySQL 5.7 and above).

SELECT 
address->>'$."addressLines"[0]' AS 'addressLine1',
address->>'$."addressLines"[1]' AS 'addressLine2',
address->>'$."city"' AS 'City', 
address->>'$."region"' AS 'Region', 
address->>'$."country"' AS 'Country', 
address->>'$."postalCode"' AS 'postalCode' 

FROM nexus_customer_addresses

The above example utilizes the shorthand for JSON_UNQUOTE(JSON_EXTRACT()):

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-inline-path

Edited December 9, 20213 yr by IPCommerceFan

On 12/7/2021 at 12:16 PM, IPCommerceFan said:

Assuming you are accessing the database directly via MySQL, you could use the JSON_EXTRACT function (MySQL 5.7 and above).

SELECT 
address->>'$."addressLines"[0]' AS 'addressLine1',
address->>'$."addressLines"[1]' AS 'addressLine2',
address->>'$."city"' AS 'City', 
address->>'$."region"' AS 'Region', 
address->>'$."country"' AS 'Country', 
address->>'$."postalCode"' AS 'postalCode' 

FROM nexus_customer_addresses

The above example utilizes the shorthand for JSON_UNQUOTE(JSON_EXTRACT()):

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-inline-path

It worked!  Thank you very much.