Optimize Your Database: Exploring MySQL 8.0 JSON Functions

JSON features in MySQL 8.0

Listing some of the JSON functions in MySQL 8.0

1. JSON_PRETTY
2. JSON_ARRAYAGG
3. JSON_MERGE
4. JSON_EXTRACT
5. JSON_STORAGE_SIZE
6. JSON_STORAGE_FREE
7. JSON_OVERLAPS
8. JSON_SCHEMA_VALID
9. JSON_SCHEMA_VALIDATION_REPORT
10. JSON_VALUE
11. MEMBER OF

1.JSON_PRETTY
The JSON data can now be fetched in a human readable format.

Advantages of JSON_PRETTY

• Makes JSON data readable.
• Useful for debugging and troubleshooting purposes.

Syntax:

1. SELECT JSON_PRETTY(json_column_name) FROM table_name ;

Sample:

1. SELECT JSON_PRETTY('{"name": "John", "age": 30, "address": {"city": "Nagercoil", "state": "Tamilnadu"}}');

Output:

1. {
2. "name": "John",
3. "age": 30,
4. "address": {
5.      "city": "Nagercoil",
6.      "state": "Tamilnadu"
7. }
8. }

2.JSON_ARRAYAGG
A Set of JSON values will be aggregated and returned as a single JSON array.

Advantages of JSON_ARRAYAGG:

• Can fetch the data as a single JSON array, instead of fetching it separately and processing it into a single array.
• Reduces code complexity.

Syntax:

1. SELECT JSON_ARRAYAGG(json_column_name) FROM table_name;

Sample:

1. SELECT JSON_ARRAYAGG(data) FROM (SELECT 1 AS data UNION ALL SELECT 2 UNION ALL SELECT 3) AS t;

Output:

1.  [1, 2, 3]

3.JSON_MERGE
Used to merge two or more JSON documents into a single JSON document.

Advantages of JSON_MERGE:

• Can easily add new information to existing JSON documents without having to modify the entire document.
• Improves performance.

Syntax:

1. SELECT JSON_MERGE(json_document[, json_document, ...]) FROM table_name;

Sample:

1. SELECT JSON_MERGE('{"name": "John", "age": 30}','{"address": {"city": "Nagercoil", "state": "Tamilnadu"}}') AS final_doc;

Output:

1. {
2. "age": 30, "name": "John", "address": {"city": "Nagercoil", "state": "Tamilnadu"}
3. }

4.JSON_EXTRACT
Used to extract values from JSON documents stored in the database using a JSON path expression.

Advantages of JSON_EXTRACT:

• The entire JSON document does not need to be processed to retrieve a specific value.
• Reduces execution time.

Syntax:

1. SELECT JSON_EXTRACT(json_document, json_path) FROM table_name;

Sample:

1. SELECT JSON_EXTRACT('{"name": "John", "age": 30, "address": {"city": "Nagercoil", "state": "Tamilnadu"}}', '$.name') AS name;

Output:

1. John

5.JSON_STORAGE_SIZE()
Shows the total number of bytes used to store the binary representation of a document.

Advantages of JSON_STORAGE_SIZE:

• View how much size is occupied by each JSON document.

Syntax:

1. SELECT JSON_STORAGE_SIZE('JSON_DOCUMENT') FROM table_name;

Sample:

1. SELECT data, JSON_STORAGE_SIZE(data) AS Size FROM bio;

Output:

1. +----------------------------------------------------+------+
2. | data                                               | Size |
3. +----------------------------------------------------+------+
4. | {"age": 24, "name": "Abish", "Hobby": "[1, 2, 3]"} |   54 |
5. +----------------------------------------------------+------+

6.JSON_STORAGE_FREE()
Shows the amount of free space gained after changes made on the document.

Advantages of JSON_STORAGE_FREE:

• View how much free space is gained after insert, update and delete operation.

Syntax:

1. SELECT JSON_STORAGE_FREE('JSON_DOCUMENT') FROM table_name;

Sample:

Insert data:

1. INSERT INTO bio VALUES ('{"age": 24, "name": "Abish", "Hobby": "[1, 2, 3]"}') ;

Update data:

1. UPDATE bio SET data = JSON_SET(data, "$.name", "AK");

• The amount of data freed after update is shown as free in bytes

1. SELECT data,JSON_STORAGE_FREE(data) AS Free FROM bio;

Output:

1. +-------------------------------------------------+------+
2. | data                                            | Free |
3. +-------------------------------------------------+------+
4. | {"age": 24, "name": "AK", "Hobby": "[1, 2, 3]"} |    9 |
5. +-------------------------------------------------+------+

7.JSON_OVERLAPS()
Compares two JSON documents and returns true (1) if the two documents have any same key-value or array elements.

Advantages of JSON_OVERLAPS:

• Check whether the document contains duplicate or identical data.

Syntax:

1. SELECT JSON_OVERLAPS( [array / key_value1] , [array / key_value2] ) ;

Sample 1:

1. SELECT JSON_OVERLAPS('{"a": 1, "b": 2}', '{"b": 3, "c": 4}');

Output:

1. 0

Sample 2:

1. SELECT JSON_OVERLAPS('{"a": 1, "b": 2}', '{"a": 1, "b": 2}');

Output:

1. 1

8.JSON_SCHEMA_VALID()

• Validates a JSON document against a JSON schema.
• The document validates against the schema, the function returns true (1) else returns false (0).

Advantages of JSON_SCHEMA_VALID() :

• Ensure data consistency: Data validates to a predefined structure and that it is consistent across multiple documents
• Improve data quality: Ensure that the data is of the expected type and that it meets the constraints defined in the schema.
• Enhance data security: Prevent malicious users from injecting invalid data into your system.

Syntax:

1. SELECT JSON_SCHEMA_VALID (@schema, @document);

Working sample:

Create Schema:

1. SET @schema = '{ "id": "101", "Schema_for_geo", "description": "A geographical coordinate", "type": "object", 
2. "properties": { 
3. "latitude": {
4. "type": "number",
5. "minimum": -90,
6. "maximum": 90
7. },
8. "longitude": {
9. "type": "number",
10. "minimum": -180,
11. "maximum": 180
12. }
13. }, "required": ["latitude", "longitude"] }';

Create Document

1. SET @document = '{ "latitude": 63.444697, "longitude": 10.445118 }' ;

Sample 1:

1. SELECT JSON_SCHEMA_VALID(@schema, @document);

Output:

1. 1

Change Document:

1. SET @document = '{}';

Sample 2:

1. SELECT JSON_SCHEMA_VALID(@schema, @document);

Output:

1. 0

9.JSON_SCHEMA_VALIDATION_REPORT()

• Validates a JSON document against a specified JSON schema and returns a validation report.

Advantages of JSON_SCHEMA_VALIDATION_REPORT()

• Provides detailed feedback on validation errors
• It returns the validation report in JSON format

Syntax:

1. SELECT  JSON_SCHEMA_VALIDATION_REPORT(schema,json_doc);

Sample:

Output scenario 1:

1. SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema,@cmp);
2. +---------------------------------------------+
3. | JSON_SCHEMA_VALIDATION_REPORT(@schema,@cmp) |
4. +---------------------------------------------+
5. | {"valid": true}                             |
6. +---------------------------------------------+

Output scenario 2:

1. SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema,@cmp)) \G

Output:

1. *************************** 1. row ***************************
2. JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema,@cmp)): {
3.   "valid": false,
4.   "reason": "The JSON document location '#/salary' failed requirement 'maximum' at JSON Schema location '#/properties/salary'",
5.   "schema-location": "#/properties/salary",
6.   "document-location": "#/salary",
7.   "schema-failed-keyword": "maximum"
8. }

10.JSON_VALUE()

• Used to extract a scalar value from a JSON document

Advantages of JSON_VALUE()

• easy to extract specific values from the structures

Syntax:

1. SELECT JSON_VALUE(json_doc, path) ;

Sample:

1. SELECT JSON_VALUE('{"name": "John", "age": 30, "isMarried": true}', '$.name');

Output:

1. +------------------------------------------------------------------------+
2. | JSON_VALUE('{"name": "John", "age": 30, "isMarried": true}', '$.name') |
3. +------------------------------------------------------------------------+
4. | John                                                                   |
5. +------------------------------------------------------------------------+

11.MEMBER OF()

• Used to check whether a value is a member of a set.

Advantages of MEMBER OF()

• Provides a simple way to check if a value is a member of a set.
• Operator works with a variety of data types, including numbers, strings, and dates

Syntax:

1. SELECT value MEMBER OF (<json_array>);

Sample:

1. SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') as member;

Output:

1. +------------+
2. | member     |
3. +------------+
4. |          1 |
5. +------------+

• Returns 1 if it is a member and 0 if not a member.