How to use the JSON type in MySQL

· Category: SQL & Databases

Short answer

MySQL's JSON type stores JSON documents in a binary format, enabling validation, indexing, and rich querying capabilities.

Steps

  1. Create a JSON column: CREATE TABLE events (id INT PRIMARY KEY, data JSON);
  2. Insert JSON: INSERT INTO events VALUES (1, '{\"user\": \"ada\", \"action\": \"login\"}');
  3. Extract values: SELECT JSON_EXTRACT(data, '$.user') FROM events;
  4. Use the arrow operators: SELECT data->'>$.user' FROM events;
  5. Create generated columns and index them for fast lookups.

Tips

  • Use generated virtual columns to index specific JSON paths.
  • Validate JSON structure at the application layer before insertion.

Common issues

  • Large JSON documents increase row size and memory usage.
  • Complex JSON queries can be slower than normalized relational queries.r