List JSON array in MySQL as rows

Question

I have a column AddressIdentifiers of JSON type in my MySQL table Customers and the data sample looks like:

[
 {
 "code": "123",
 "identifier": "0219d5780f6b",
 "type": "BILLING",
 "info": null
 },
 {
 "code": "240",
 "identifier": "c81aaf2c5a1f",
 "type": "DELIVERY",
 "info": null
 }
]

I am using query:

SELECT JSON_EXTRACT(AddressIdentifiers, '$**.identifier') As Identifier, JSON_EXTRACT(AddressIdentifiers, '$**.type') As AddressType FROM Customers WHERE CustomerId = 10003;

to get output like:

Identifier AddressType
------------------------
0219d5780f6b BILLING
c81aaf2c5a1f DELIVERY

But it does not give output like table rows instead gives concatenated values. How can we get the output from the JSON array like rows (as above)? Please advise.

I am using MySQL version 5.7.17.

Thanks,
Prabhat

EDIT-1:
I think if we join this Customers table with Address table (having columns: Id, identifier, Address, City, Pincode) that has the identifier as key then may be we can pull these values as row for each address?

So output will be something like:

 Identifier AddressType Address City
 -----------------------------------------
 0219d5780f6b BILLING Address-1 Hyderabad
 c81aaf2c5a1f DELIVERY Address-2 Delhi

• Bishwas Mishra: A good solution is here: stackoverflow.com/a/42153230/5381704

Answer 1

I have this solution for MySQL 5.7, where you have to do the work manually. In the case of MySQL 8.0+ you can simply use JSON_TABLE

SELECT
 JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].Identifier')) AS Identifier,
 JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].AddressType')) AS AddressType,
FROM
(
 SELECT @row := @row + 1 AS N FROM 
 (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2,
 (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1, 
 (SELECT @row:=0) T0
) Numbers -- Natural numbers from 1 to 100
INNER JOIN Customers C ON Numbers.N <= JSON_LENGTH(C.AddressIdentifiers)