MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL JSON Tricks

without comments

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. Check this post to see how to insert a result set without Python as an intermediary.

Written by maclochlainn

June 23rd, 2022 at 12:46 am

Posted in JSON,MySQL,MySQL 8,Python,Python 3.x,sql

Tagged with , ,

«
»