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