How to create a JSON array of structs from MySQL using MySQLdb and have control of JSON stylized field names?

user_18629827 0
Viewed 40

I have a semi-complex Python / MySQLdb problem with selecting data from a database and converting that to a specific JSON structure.

I will edit this post with an updated working solution, once working.

First the database table

  • table:'person'
    • id
    • first_name
    • last_name

Each row represents a person. For the json output, I need an array of structs. Each element of the array is a struct that represents a row in the table.

Requirements:

  • I am using Python with MySQLdb to select the data, and so need a solution that works with MySQLdb
  • The starting and end character would have to be '{' and '}', and not '[' and ']'
  • The json stylized fields may be named differently from the DB table field names, so I need to have control of that
  • The DB table name, may be different than what I want or need for the name of the json array, so I need control of that

I want to create an array of structs that would look something like this, where each row would be an element in the array:

{
   "persons": [
    {
       "personId": 1,
       "firstName": "<first_name>",
       "lastName": "<last_name>"
    },
    {
       "personId": 2,
       "firstName": "<first_name>",
       "lastName": "<last_name>"
    }
   ]
}
mysql-python mysql python json
1 Answers

You can do this in SQL this way (assuming MySQL 5.7.22 or later)

SELECT JSON_OBJECT(
    'persons',
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'personId', id,
        'firstName', first_name,
        'lastName', last_name
      )
    )
  ) AS `result`
FROM mytable;

Alternatively, you could do a simple query, fetch the results, and build an array of dict objects in Python, then convert the whole array to JSON using json.dumps().

Bill_Karwin_208600
Related