MariaDB JSON Powerhouse: Mastering Value Extraction with JSON_EXTRACT

2024-07-27

JSON_EXTRACT(json_doc, path [, path]...):
- json_doc: This is the name of the column containing the JSON data you want to extract from.
- path: This is a JSONPath expression that specifies the location of the value you want to extract within the JSON document. You can provide multiple paths (separated by commas) to extract values from different locations.

Understanding JSONPath Expressions:

JSONPath expressions use a dot notation (. ) to navigate through nested objects and brackets ([]) to access elements within arrays.
The $ symbol represents the root of the JSON document.

Examples:

Extracting a value from a key in an object:

SELECT JSON_EXTRACT(data, '$.name')
FROM my_table;

This query assumes you have a table named my_table with a column named data containing JSON documents. It extracts the value associated with the key "name" from each document.

Extracting an element from an array:

SELECT JSON_EXTRACT(data, '$.items[1]')
FROM my_table;

This query extracts the second element (index 1) from the array identified by the key "items" within each JSON document.

Extracting multiple values:

SELECT JSON_EXTRACT(data, '$.name'), JSON_EXTRACT(data, '$.age')
FROM my_table;

This query extracts both the value for "name" and "age" from each JSON document and returns them as separate columns.

Additional Notes:

JSON_EXTRACT returns NULL if the specified path doesn't exist or if the json_doc is invalid.

CREATE TABLE my_data (
  id INT PRIMARY KEY,
  data JSON
);

INSERT INTO my_data (id, data) VALUES
  (1, '{"name": "Alice", "age": 30}');

SELECT id, JSON_EXTRACT(data, '$.name') AS name
FROM my_data;

This code:

Creates a table my_data with columns id and data.
Inserts a sample JSON document into the data column for record with id 1.
Selects id and uses JSON_EXTRACT to retrieve the value associated with "name" and aliases it as name.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  details JSON
);

INSERT INTO products (product_id, details) VALUES
  (101, '{"name": "Shirt", "sizes": ["S", "M", "L"]}');

SELECT product_id, JSON_EXTRACT(details, '$.sizes[1]') AS size
FROM products;

Inserts a sample JSON document with an array for sizes into the details column.
Selects product_id and uses JSON_EXTRACT to retrieve the second element (index 1) from the "sizes" array, aliasing it as size.

Extracting multiple values with aliases:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  info JSON
);

INSERT INTO users (user_id, info) VALUES
  (22, '{"username": "johndoe", "email": "[email protected]"}');

SELECT user_id, 
  JSON_EXTRACT(info, '$.username') AS username, 
  JSON_EXTRACT(info, '$.email') AS email
FROM users;

Creates a table users with columns user_id and info.
Inserts a sample JSON document with username and email keys.
Selects user_id and uses JSON_EXTRACT twice to retrieve both "username" and "email" values, assigning them aliases username and email.

Using the -> operator (MariaDB 10.3 or later):

MariaDB 10.3 introduced the -> operator, which allows a more intuitive way to navigate through JSON objects. Here's an example:
```
SELECT id, data->'$.name' AS name
FROM my_data;
```
This achieves the same result as the first example using JSON_EXTRACT, but with a simpler syntax for accessing object keys.
Casting to JSON and then using standard operators (limited functionality):

This method involves casting the JSON column to a JSON data type (available in some MariaDB versions) and then using standard comparison operators to access specific values. However, this approach has limitations:
- It's not as versatile as JSON_EXTRACT for complex navigation within JSON documents.
- Not all MariaDB versions support casting to JSON data types.

Here's an example (might not work on all MariaDB versions):

SELECT id, CAST(data AS JSON)->>'$.name' AS name
FROM my_data;

This example casts the data column to JSON and then uses the ->> operator (might not be supported) to access the "name" key's value.

Choosing the Right Method:

For most cases, JSON_EXTRACT remains the recommended approach due to its clarity, flexibility, and wide support across MariaDB versions.
If you're using MariaDB 10.3 or later and prefer a more concise syntax for simple object access, the -> operator is a good alternative.
The casting method is generally not recommended due to limitations and should only be considered if JSON_EXTRACT and -> are not suitable for your specific scenario.

mariadb