Demystifying the Difference: JSON_QUERY vs. JSON_VALUE in MariaDB

JSON_VALUE

• Syntax
  
• Output
  Returns a string, number, boolean, or null value.
• Purpose
  Extracts a scalar value (single atomic data type) from a JSON document.

JSON_VALUE(json_document, path)

• path: A JSON path expression that specifies the location of the value within the document. The path uses dot notation (.) for object members and square brackets ([]) for array elements.
• json_document: The JSON string to extract the value from.

Example

SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.name') AS name;

This query extracts the value of the "name" property (which is "Alice") and assigns it to the alias name.

JSON_QUERY

• Syntax
  
• Output
  Returns a JSON object or array containing the extracted value.
• Purpose
  Navigates and extracts a complex value (object or array) from a JSON document.

JSON_QUERY(json_document, path)

• Same parameters as JSON_VALUE.

Example

SELECT JSON_QUERY('{"address": {"street": "123 Main St", "city": "Anytown"}}', '$.address') AS address;

This query extracts the entire "address" object (including "street" and "city" properties) and assigns it to the alias address.

Key Differences

Feature	JSON_VALUE	JSON_QUERY
Output Type	Scalar value (string, number, boolean, null)	Complex value (object or array)
Nesting Support	Supports nested objects and arrays	Supports nested objects and arrays
Wildcard Support	No	Limited (can only use . for all descendants)

Choosing the Right Function

• Use JSON_QUERY when you need to extract a more complex value (object or array) or navigate through a nested JSON structure.
• Use JSON_VALUE when you need to extract a single value from a specific location in the JSON document.

• MariaDB offers other JSON functions like JSON_EXTRACT for more advanced navigation and extraction scenarios.
• Both functions return NULL if the specified path is not found in the JSON document.

---

Extracting Nested Values with JSON_QUERY

SELECT JSON_QUERY('{"data": {"items": [{"name": "Item 1", "price": 10}, {"name": "Item 2", "price": 20}]}}', '$.data.items[1].price') AS second_item_price;

This query retrieves the price (20) of the second item in the "items" array within the nested "data" object.

Handling Arrays with JSON_VALUE (First Element)

SELECT JSON_VALUE('{"colors": ["red", "green", "blue"]}', '$.colors[0]') AS first_color;

This query gets the first element ("red") from the "colors" array using JSON_VALUE. However, keep in mind that JSON_VALUE is limited for extracting specific elements within arrays.

Using JSON_QUERY for All Elements in an Array

SELECT JSON_QUERY('{"numbers": [1, 2, 3]}', '$.numbers') AS all_numbers;

This query extracts the entire "numbers" array ([1, 2, 3]) using JSON_QUERY.

Handling Missing Values and Errors

SELECT
  JSON_VALUE('{"name": "Alice"}', '$.age') AS age,  -- Returns NULL (age property missing)
  JSON_VALUE('{"invalid_json": true}', '$') AS invalid_json_error  -- Returns NULL (invalid JSON)
FROM your_table;

This code demonstrates how both functions handle missing values and invalid JSON input.

Combining JSON_VALUE and JSON_QUERY (Extracting Specific Properties from Nested Objects)

SELECT
  JSON_QUERY('{"data": {"products": [{"id": 123, "details": {"name": "Product A"}}]}}', '$.data.products[0]') AS product,
  JSON_VALUE('{"data": {"products": [{"id": 123, "details": {"name": "Product A"}}]}}', '$.data.products[0].details.name') AS product_name
FROM your_table;

This query shows how you can combine JSON_QUERY to retrieve a nested object and then use JSON_VALUE to extract a specific property from that object.

---

Interactive Learning Platforms

• MariaDB Interactive Shell (available in some MariaDB installations) allows you to directly execute queries and test their behavior with sample JSON.

Visualizations

Online Documentation and Tutorials

Community Forums and Stack Overflow

• If you prefer text-based learning, find tutorials dedicated to using JSON functions in MariaDB. These can provide a more structured approach to learning the differences between JSON_QUERY and JSON_VALUE.