How to query a JSON MySQL column into a dict?

Question

My database looks like this:

CREATE TABLE my_table( irrelevant_column TEXT, json_stuff JSON );

mysql> SELECT * FROM my_table;

+------------------------+-----------------------------------------------+
| irrelevant_column      | json_stuff                                    |
+------------------------+-----------------------------------------------+
| blah blah              | { 'this': 'is my json data' }                 |
| more unrelated stuff   | { 'more': 'of my data in the correct format' }|
+------------------------+-----------------------------------------------+

I am looking for an elegant way to query JSON data from MySQL into a list of dicts.

I have tried using a DictCursor

cursor = connection.cursor( pymysql.cursors.DictCursor )
cursor.execute( "SELECT json_stuff FROM my_table" )
rows = cursor.fetchall()

but it doesn't properly handle the JSON column type. It returns this:

[
  { "json_stuff": "<json_stuff row 1 in string format>" },
  { "json_stuff": "<json_stuff row 2 in string format>" }, 
  etc.
]

I would like

[
  { 'this': 'is my json data' },
  { 'more': 'of my data in the correct format' }, 
  etc.
]

This ugly and inefficient code works.

def get_list_of_stuff():
    cursor = connection.cursor()
    cursor.execute( "SELECT json_stuff FROM my_table" )
    rows = cursor.fetchall()
    return [ json.loads( row["json_stuff"] ) for row in rows ]

Does anyone know a way to do this without looping through all of the rows and parsing each one into JSON?

Have you tried to fetch the JSON stuff as a string, and then reading it in via the `json` module? — Green Cloak Guy, Jul 11 '19 at 21:19
`this` and `more` are keys in the JSON data in the `json_stuff` column. — Stefan, Jul 11 '19 at 21:22
It works with but one extra line, that's not "ugly" in my books. And it's rather efficient in terms of time spent on writing it. Please specify your requirements. — IcedLance, Jul 11 '19 at 21:55
I'm looking for a "native" way to go directly from MySQL-native JSON format to Python-native JSON format. Note that this is possible when going in the other direction (`INSERT`). — Stefan, Jul 11 '19 at 22:07

score 4 · Accepted Answer · answered Jul 11 '19 at 22:06

The MySQL developers apparently don't consider it appropriate for drivers to automatically convert between the JSON data type and Python structured types. A bug report JSON columns should accept Python dicts as input was submitted a couple of years ago. It was closed as "Not a bug", with the comment:

Because JSON is not a built-in type (or even type in Python). This kind of conversions shouldn't be made in a driver, is better suited for frameworks that have an high level of abstraction.

Since you're not using a framework, just a low-level database API, you need to do the parsing yourself.

Peronally, I disagree with their reasoning, and I think your expectation is reasonable. But that's the way it is.

The discussion in that report is interesting and captures the spirit of what I'm trying to do. With sad trombone music I'm afraid I must accept this answer. — Stefan, Jul 11 '19 at 22:15

How to query a JSON MySQL column into a dict?

1 Answers1