Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
DolSch Posted December 7, 2021 Posted December 7, 2021 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?
Solution IPCommerceFan Posted December 7, 2021 Solution Posted December 7, 2021 (edited) 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, 2021 by IPCommerceFan
DolSch Posted December 18, 2021 Author Posted December 18, 2021 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. IPCommerceFan 1
Recommended Posts