Convert json string to rows in mysql

Question

How can I convert this json string to a mysql row in mysql?

"21283":{"count":82,"price":444},"21158":{"count":178,"price":414},"21281":{"count":157,"price":216},"21165":{"count":132,"price":858},"21284":{"count":99,"price":407},"21175":{"count":60,"price":1650},"21282":{"count":50,"price":1440},"21168":{"count":14,"price":4715},"21280":{"count":22,"price":1625}

Is there any solution for this?

What is the source of this JSON string? What would the record(s) in MySQL look like? — Tim Biegeleisen, Jun 23 '19 at 13:43
@TimBiegeleisen for example "21283" is product_id of a table,and i want to convert this string to this type: `|Product_Id|count|price| |21283 |82 |444 |` — Parsa Saei, Jun 23 '19 at 13:55
Don't make it a comment, we rather see this [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) for providing example data and expected results and [edit](https://stackoverflow.com/posts/56724408/edit) the question with that information. — Raymond Nijland, Jun 23 '19 at 14:07
Also we need to know MySQL version (`SELECT VERSION()`) as the native JSON functions are very differenct or not existing at all between MySQL versions.. i hope you atleast have MySQL 5.7.17+.. — Raymond Nijland, Jun 23 '19 at 14:09
But still the answer will be tricky as your JSON keys have dynamic values, but when looking closer i noticed it isn't even valid JSON it is missing a enclosing `{}` — Raymond Nijland, Jun 23 '19 at 14:14
@RaymondNijland Can I convert that to a row of table with "product_id" , "count" , "price" fields? version of mysql is greater than 5.7.17. — Parsa Saei, Jun 23 '19 at 16:03
The question is why not use a table and save the data into that table? `table_name: product_id, count, price` ? So you would never have to convert it again? JSON structure is pretty much "misused" here.. — Raymond Nijland, Jun 23 '19 at 16:06
@RaymondNijland I have a table with product name field which stores data like the json I exampled it and I want to convert this field to rows to can make the relations and tables related to that — Parsa Saei, Jun 23 '19 at 16:09
@RaymondNijland Iwant to make the convert query and finally import the output of query to the table with structure `table_name: product_id, count, price` as you said — Parsa Saei, Jun 23 '19 at 16:13
the example data isn't even json like i have mentioned before, test it here https://jsonlint.com/ .. so converting isn't really possible without tricks.. — Raymond Nijland, Jun 23 '19 at 16:14

score 1 · Accepted Answer · answered Jun 24 '19 at 00:46

Based on your comments, it sound like this is what you want to do:

import json
import pymysql # I am using Python 3

# string is obtained from somewhere (with extra '{' added at beginning and '}' added at end to make it valid JSON)
rec_string = '{"21283":{"count":82,"price":444},"21158":{"count":178,"price":414},"21281":{"count":157,"price":216},"21165":{"count":132,"price":858},"21284":{"count":99,"price":407},"21175":{"count":60,"price":1650},"21282":{"count":50,"price":1440},"21168":{"count":14,"price":4715},"21280":{"count":22,"price":1625}}'
# and converted to a dictionary:
rec = json.loads(rec_string)

conn = pymysql.connect(db='my_db', user='xxxxxxxx', passwd='xxxxxxxx')
cursor = conn.cursor()
for k,v in rec.items():
    cursor.execute('insert into test_table(product_id, count, value) values(%s, %s, %s)', (k, v['count'], v['price']))
conn.commit()

Convert json string to rows in mysql

1 Answers1