1

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
  • MongoDB is fully supported the (.json) document. Json format is document oriented and MongoDB (No SQL ) database is document oriented database. But MySQL is support RDBMS. – Md Haidar Ali Khan Nov 9 '17 at 12:24
  • 1
    MySQL's JSON support is still not really mature compared to the JSON support from PostgreSQL.. PostgreSQL has the function json_each() to convert JSON into records.. also it doesnt look like MySQL 8+ will support such of feature dev.mysql.com/doc/refman/8.0/en/json-function-reference.html – Raymond Nijland Nov 9 '17 at 13:45
  • 1
    JSON is meant to be an addon to RDBMS, not a replacement for the rich features of relational databases. – Rick James Nov 9 '17 at 19:27
  • @RaymondNijland - asking it to do what the OP asks is hardly a leap if you're going to offer JSON functionality! – Vérace Nov 9 '17 at 19:50
  • May be if we can join the table with other table that has the identifier then may be we flatten the values. Just added EDIT-1 in my question. – Prabhat Nov 11 '17 at 5:09
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.