I arrived at this problem because I was trying to do the same thing: Import a JSON object into MYSQL so that I could query it.
Perhaps I have simplified the original questions (I can't actually see the desired input file) but my solution was to simply input the JSON data as one single JSON object. Then I was able to use the MYSQL commands to expand the JSON object directly in MYSQL and complete the task.
In Python I created an endpoint class that:
- Took in a JSON object
- "Dumped" it into a variable called jsonBOM
- Ran a query to insert the JSON object into an existing table.
Once I had it in an SQL table I could use all the regular JSON commands to extract the data into a table. (I've given my endpoint class below for reference but everyone creates endpoints differently and I have not included all the helper functions. I just wanted to show how the query is wrapped within python)
class Allocation(Resource):
def post(self):
response = {}
items = {}
try:
conn = connect()
allocation_data = request.get_json(force=True)
jsonBOM = json.dumps(allocation_data)
new_allocation_uid = get_new_allocationUID(conn)
today = getNow()
# Run query to enter JSON object into Allocation table
addAllocation = """
INSERT INTO pmctb.allocation
SET allocation_uid = \'""" + new_allocation_uid + """\',
allocation_date = \'""" + today + """\',
allocation_json = \'""" + jsonBOM + """\';
"""
items = execute(addAllocation, "post", conn)
response['allocation'] = "Successful"
return response
except:
raise BadRequest('Add Part failed')
finally:
disconnect(conn)
Now I could a JSON input like this:
[
{"product_uid":"310-000594", "inv_uid":"870-000016", "assembly":"C", "allocated":3},
{"product_uid":"310-000594", "inv_uid":"870-000016", "assembly":"C", "allocated":2}
]
And then create a table in MySQL using a query like this (again, this code is just to show that the JSON object can be made into a table):
JSON_TABLE (p.allocation_json, '$[*]'
COLUMNS (
Allocation_id FOR ORDINALITY,
Allocation_product_uid VARCHAR(255) PATH '$.product_uid',
Allocation_inventory_uid VARCHAR(255) PATH '$.inv_uid',
Allocation_assy_name VARCHAR(255) PATH '$.assembly',
Allocation_allocated_qty INT PATH '$.allocated')
) AS BOM
I hope that helps someone!
{ "id": "24522982_585115708186764", "from": { "name": "Ma Mei", "id": "100005547752625" }, "to": { "data": [ { "name": "Wohnung/WG in München gesucht!", "id": "245226895508982" } ] }, "message": "Hallo,\n\nsuchen eine 3 oder 4 Zimmer Wohnung, die als WG für 3 oder 4 Personen, je nachdem wieviele Zimmer, geeignet ist. Ab sofort. Können jederzeit eine Besichtigung wahrnehmen. 3 Studenten, 1 Arbeiter. Freue mich über jeden Hinweis." }– Sodden