Python: How to retrieve and parse JSON array data from a MySQL field in Zapier

Other Questions Zapier
1

Introduction

I’m a beginner in both Python and Zapier, and I’m encountering some hurdles. In my MySQL database, I have a column dedicated to storing JSON array data. I’ve managed to access this field through Zapier, but I’m having trouble with extracting specific values from the JSON format.

Objective

My goal is to extract a value labeled ‘postcode’ from this JSON array using Python code within my Zapier automation. The JSON structure is somewhat complex with nested objects, and I only want to isolate that specific piece of information.

Current status

I’ve tried to write some simple Python code for this task, but I’m not seeing the results I expected. Although the JSON data appears to be correctly fetched from the database, I’m facing challenges when attempting to parse it and retrieve the postcode value.

Example of JSON layout

When decoded, the data resembles the following:

[
  {
    "address_info": {
      "street": "123 Main St",
      "postcode": "12345",
      "city": "Sample City"
    }
  }
]

My code trial

import json

# Access JSON data from MySQL field
raw_data = input_data['mysql_json_field']

# Attempt to parse and extract the postcode
try:
    parsed_data = json.loads(raw_data)
    target_postcode = parsed_data[0]['address_info']['postcode']
    output = {'extracted_postcode': target_postcode}
except Exception as e:
    output = {'error': str(e)}

I would really appreciate any help on what I might be doing incorrectly. Any advice on managing JSON arrays in Zapier would be hugely valuable.

2

This sounds like a Zapier + MySQL data type issue. Zapier sometimes double-encodes JSON or adds extra characters when pulling from MySQL. I’ve run into this before - adding a quick validation step before parsing usually fixes it. Check if raw_data is already a Python object instead of a string first. Zapier sometimes pre-processes JSON fields, so try if isinstance(raw_data, str): before your json.loads() call. Also, MySQL JSON fields can include BOM characters at the start that’ll break parsing. Use raw_data.lstrip('\ufeff') to strip those out. One more thing - your code assumes there’s always an element at index 0, but database queries can return empty arrays. Worth adding a check for that too.

3

MySQL’s probably returning your JSON as a string with escaped quotes or weird characters. I’ve hit this before - you get something like \"{\\\"address_info\\\":{...}} instead of clean JSON. Debug it by printing the raw data first. Add print(repr(raw_data)) before your json.loads() call to see what you’re actually getting. Also, some MySQL setups return JSON fields as binary data, so try raw_data.decode('utf-8') if it’s bytes. And check if your JSON array is empty after parsing - add a length check to avoid index errors.

4

your code looks fine. could be extra whitespace or encoding issues in the mysql field. try adding raw_data = raw_data.strip() before parsing. also check if the data’s already a dict instead of a string - zapier sometimes does weird stuff with json fields.