MySQL has supported the native JSON data type since version 5.7, enabling efficient storage and querying of structured data such as arrays. Representing complex collections as JSON arrays preserves logical relationships while keeping the format portable across platforms.

Table Schema for JSON Storage

Define a table with a column of type JSON to hold array data:

CREATE TABLE client_profiles (
    profile_id INT PRIMARY KEY,
    full_name VARCHAR(50),
    attributes JSON
);

The attributes field can store an array or object encoded in JSON.

Inserting a JSON Array

A JSON array can be inserted directly as a string literal that conforms to JSON syntax:

INSERT INTO client_profiles (profile_id, full_name, attributes)
VALUES (101, 'Alice', '[\"red\", \"green\", \"blue\"]');

MySQL parses and validates the text as JSON before persisting it.

Progrmamatic Insertion in Python

Using PyMySQL, serialize a native list into a JSON string and insert it into the table:

import pymysql
import json

conn = pymysql.connect(
    host='127.0.0.1',
    user='admin',
    password='secret',
    database='sample_db',
    charset='utf8mb4'
)
cur = conn.cursor()

# Ensure table exists
cur.execute(
    "CREATE TABLE IF NOT EXISTS client_profiles ("
    "profile_id INT PRIMARY KEY, "
    "full_name VARCHAR(50), "
    "attributes JSON)"
)

color_list = ['red', 'green', 'blue']
payload = json.dumps(color_list)

insert_stmt = (
    "INSERT INTO client_profiles (profile_id, full_name, attributes) "
    "VALUES (%s, %s, %s)"
)
cur.execute(insert_stmt, (101, 'Alice', payload))

conn.commit()

# Retrieve and display stored record
cur.execute("SELECT * FROM client_profiles WHERE profile_id = 101")
row = cur.fetchone()
print(row)

cur.close()
conn.close()

The script connects to MySQL, ensures the target table exists, serializes a Python list into JSON, performs the insertion, commits the transaction, and fetches the stored row.

Querying Elements Inside a JSON Array

MySQL provides functions to inspect and extract elements from JSON columns. For example, to check whether a given value exists in the array:

SELECT full_name 
FROM client_profiles 
WHERE JSON_CONTAINS(attributes, '\"green\"');

To retrieve the first element:

SELECT JSON_UNQUOTE(JSON_EXTRACT(attributes, '$[0]')) AS first_color
FROM client_profiles;

These capabilities allow indexing-free queries over array contents while leveraging MySQL's JSON validation and storage optimizations.