Are they really tricks or simply basic techniques combined to create a solution. Before writing these mechanics for using native MySQL to create a compound JSON object, let me point out that the easiest way to get one is to use the MySQL Node.js library, as shown recently in my “Is SQL Programming” blog post.
Moving data from a relational model output to a JSON structure isn’t as simple as a delimited list of columns in a SQL query. Let’s look at it in stages based on the MySQL Server 12.18.2 Functions that create JSON values.
Here’s how you return single row as a JSON object, which is quite straightforward:
SELECT JSON_OBJECT('first_name',c.first_name,'last_name',c.last_name) AS json_result
FROM contact c
WHERE first_name = 'Harry'
AND last_name = 'Potter';
It returns:
+------------------------------------------------+
| json_result |
+------------------------------------------------+
| {"last_name": "Potter", "first_name": "Harry"} |
+------------------------------------------------+
1 row in set (0.00 sec)
With a GROUP_CONCAT function, let’s capture a JSON array of all three Potter family members:
SELECT CONCAT('['
, GROUP_CONCAT(
JSON_OBJECT('first_name',first_name
,'last_name',last_name ) SEPARATOR ',')
,']') AS json_result
FROM contact c
WHERE c.last_name = 'Potter';
It returns an array of JSON objects:
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| [{"last_name": "Potter", "first_name": "Harry"},{"last_name": "Potter", "first_name": "Ginny"},{"last_name": "Potter", "first_name": "Lily"}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Next, let’s put a 1:many relationship between the member and contact table into a JSON structure with a single account number and an array of contact. It requires a second call to the JSON_OBJECT function and the addition of a GROUP BY clause in the query.
SELECT JSON_OBJECT(
'account_number', account_number
,'contact', CONCAT('['
, GROUP_CONCAT(
JSON_OBJECT('first_name',first_name
,'last_name',last_name ) SEPARATOR ',')
,']')
) AS json_result
FROM member m INNER JOIN contact c
ON m.member_id = c.member_id
WHERE c.last_name = 'Potter'
GROUP BY m.account_number;
It returns the following string with an annoying set of backslashes. It also inverts the column order, which appears unavoidable but it shouldn’t matter because the order of name-value pairs in JSON is immaterial.
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"contact": "[{\"last_name\": \"Potter\", \"first_name\": \"Harry\"},{\"last_name\": \"Potter\", \"first_name\": \"Ginny\"},{\"last_name\": \"Potter\", \"first_name\": \"Lily\"}]", "account_number": "US00011"} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The following quick little Python code cleans up the JSON string by removing the backslashes and extraneous quotes around the array of contacts.
# Import the library.
import mysql.connector
from mysql.connector import errorcode
try:
# Open connection.
cnx = mysql.connector.connect(user='student', password='student',
host='127.0.0.1',
database='studentdb')
# Create cursor.
cursor = cnx.cursor()
# Set the query statement.
query = ("SELECT JSON_OBJECT( "
"'account_number', m.account_number "
",'contact', CONCAT('[' "
" , GROUP_CONCAT( "
" JSON_OBJECT('first_name', c.first_name "
" ,'last_name', c.last_name ) SEPARATOR ',') "
" ,']')) AS json_result "
"FROM contact c INNER JOIN member m "
"ON c.member_id = m.member_id "
"WHERE c.last_name = %s "
"GROUP BY account_number")
# Execute cursor.
cursor.execute(query,["Potter"])
# Display the column returned by the query stripped of backslashes and
# extraneous quotes.
for (row) in cursor:
for column in range(len(row)):
print(row[column].replace("\\","").replace("\"[","[").replace("]\"","]"))
# Close cursor.
cursor.close()
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif e.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print("Error code:", e.errno) # error number
print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
print("Error message:", e.msg) # error message
# Close the connection when the try block completes.
else:
cnx.close()
It returns:
{"contact": [{"last_name": "Potter", "first_name": "Harry"},{"last_name": "Potter", "first_name": "Ginny"},{"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"}
I hope this helps exhibit less well known MySQL syntax.
Planet MySQL