Mysql – List JSON array in MySQL as rows

jsonMySQL

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

Best Answer

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)
Related Question