MySQL JSON Path Tester

Test and validate MySQL JSON path expressions before using them in your queries. This tool helps you understand how JSON_EXTRACT() and related JSON functions work with your data.

What is JSON_EXTRACT?

JSON_EXTRACT() is a MySQL function that extracts data from JSON columns using path expressions. It’s essential when working with JSON data stored in MySQL databases.

Basic Syntax

SELECT JSON_EXTRACT(column_name, '$.path.to.value') FROM table_name;

Path Syntax

Root Object

• $ - Returns the entire JSON document

Object Properties

• $.key - Access a top-level property
• $.nested.property - Access nested properties
• $['key-with-dash'] - Access keys with special characters

Array Elements

• $.array[0] - First element (zero-indexed)
• $.array[2] - Third element
• $.users[0].name - Property of array element

Wildcards

• $[*] - All array elements
• $[*].id - Extract specific property from all elements

Common Use Cases

E-Commerce Product Attributes

{
  "name": "Laptop",
  "specs": {
    "cpu": "Intel i7",
    "ram": "16GB"
  },
  "tags": ["electronics", "computers"]
}

Paths:

• $.name → “Laptop”
• $.specs.cpu → “Intel i7”
• $.tags[0] → “electronics”

User Preferences

{
  "theme": "dark",
  "notifications": {
    "email": true,
    "push": false
  }
}

Paths:

• $.theme → “dark”
• $.notifications.email → true

Nested Arrays

{
  "orders": [
    {"id": 1, "total": 99.99},
    {"id": 2, "total": 149.99}
  ]
}

Paths:

• $.orders[0].id → 1
• $.orders[1].total → 149.99

MySQL JSON Functions

Once you’ve tested your paths, use them with these MySQL functions:

JSON_EXTRACT()

Extract values from JSON documents:

SELECT JSON_EXTRACT(data, '$.user.email') FROM users;

JSON_SEARCH()

Find the path to a value:

SELECT JSON_SEARCH(data, 'one', 'electronics', NULL, '$.tags[*]');

JSON_CONTAINS()

Check if a value exists:

SELECT * FROM products WHERE JSON_CONTAINS(tags, '"electronics"');

JSON_SET()

Update or insert values:

UPDATE products
SET data = JSON_SET(data, '$.price', 29.99)
WHERE id = 1;

Tips

1. Always quote strings in paths: Use $.key not $key
2. Arrays are zero-indexed: First element is [0], not [1]
3. Test complex paths first: Use this tool before writing queries
4. Handle NULL values: JSON_EXTRACT() returns NULL if path doesn’t exist
5. Use JSON_UNQUOTE(): Remove quotes from extracted string values

Performance Considerations

• Index virtual columns: Create generated columns with indexes for frequently queried paths:

  ALTER TABLE products
  ADD COLUMN price_extracted DECIMAL(10,2)
  AS (JSON_EXTRACT(data, '$.price')),
  ADD INDEX idx_price (price_extracted);

• Avoid wildcards in WHERE: Specific paths are much faster than wildcards

• Store frequently accessed data normally: Don’t over-use JSON columns

MySQL Version Requirements

• JSON support: MySQL 5.7.8+
• JSON functions: Most functions available in MySQL 5.7+
• Improved performance: MySQL 8.0+ has significant JSON improvements