How the JSON_LOOSE() function works in Mariadb?

JSON is a popular data format for storing and exchanging structured data. It is widely used in web development, data analysis, and other applications. JSON documents are composed of key-value pairs, arrays, and literals, such as strings, numbers, booleans, and nulls. JSON documents can be formatted in different ways, such as compact, pretty, or loose.

In this article, we will introduce the JSON_LOOSE() function in Mariadb, which is a built-in function that adds spaces to a JSON document to make it more readable. We will also show some examples of how to use this function, and compare it with some related functions, such as JSON_DETAILED() and JSON_COMPACT().

Syntax

The syntax of the JSON_LOOSE() function is as follows:

JSON_LOOSE(json_doc)

The function takes one argument, which is a JSON document or a valid expression that returns a JSON document. The function returns a new JSON document that is formatted with spaces between the elements. The function does not change the original JSON document or its structure.

Examples

Let’s see some examples of how to use the JSON_LOOSE() function in Mariadb.

Example 1: Simple JSON document

Suppose we have a simple JSON document that contains some information about a person, such as name, age, and hobbies. The JSON document is stored in a variable called @j1, as shown below:

SET @j1 = '{"name":"Alice","age":25,"hobbies":["reading","music","traveling"]}';

We can use the JSON_LOOSE() function to format this JSON document with spaces, as follows:

SELECT JSON_LOOSE(@j1);

The output of the above query is:

{"name": "Alice", "age": 25, "hobbies": ["reading", "music", "traveling"]}

We can see that the JSON_LOOSE() function adds spaces after the colons and commas, and before and after the brackets and braces. This makes the JSON document more readable and human-friendly.

Example 2: Nested JSON document

Suppose we have a nested JSON document that contains some information about a product, such as name, price, and specifications. The specifications are also a JSON document that contains some details, such as color, size, and weight. The JSON document is stored in a variable called @j2, as shown below:

SET @j2 = '{"name":"iPhone 12","price":799,"specifications":{"color":"black","size":"6.1 inches","weight":"164 grams"}}';

We can use the JSON_LOOSE() function to format this JSON document with spaces, as follows:

SELECT JSON_LOOSE(@j2);

The output of the above query is:

{"name": "iPhone 12", "price": 799, "specifications": {"color": "black", "size": "6.1 inches", "weight": "164 grams"}}

We can see that the JSON_LOOSE() function adds spaces to the nested JSON document as well, but it does not add any indentation or line breaks to emphasize the nested structure. This makes the JSON document more readable, but not as detailed as the JSON_DETAILED() function, which we will see in the next section.

Example 3: JSON document with null values

Suppose we have a JSON document that contains some null values, such as an empty array, an empty object, or a literal null. The JSON document is stored in a variable called @j3, as shown below:

SET @j3 = '{"array":[],"object":{},"null":null}';

We can use the JSON_LOOSE() function to format this JSON document with spaces, as follows:

SELECT JSON_LOOSE(@j3);

The output of the above query is:

{"array": [], "object": {}, "null": null}

We can see that the JSON_LOOSE() function adds spaces to the empty array, the empty object, and the null value, but it does not change their representation. This makes the JSON document more readable, but not as compact as the JSON_COMPACT() function, which we will see in the next section.

Related Functions

In this section, we will briefly introduce some related functions that can also format JSON documents in different ways, such as JSON_DETAILED(), JSON_COMPACT(), and JSON_PRETTY().

JSON_DETAILED()

The JSON_DETAILED() function is similar to the JSON_LOOSE() function, but it also adds indentation and line breaks to the JSON document to emphasize the nested structure. For example, if we apply the JSON_DETAILED() function to the JSON document stored in @j2, we will get the following output:

{
  "name": "iPhone 12",
  "price": 799,
  "specifications": {
    "color": "black",
    "size": "6.1 inches",
    "weight": "164 grams"
  }
}

We can see that the JSON_DETAILED() function makes the JSON document more detailed and structured, but also more verbose and less compact.

JSON_COMPACT()

The JSON_COMPACT() function is the opposite of the JSON_LOOSE() function, as it removes all the spaces from the JSON document to make it more compact and efficient. For example, if we apply the JSON_COMPACT() function to the JSON document stored in @j3, we will get the following output:

{"array":[],"object":{},"null":null}

We can see that the JSON_COMPACT() function makes the JSON document more compact and efficient, but also less readable and human-friendly.

JSON_PRETTY()

The JSON_PRETTY() function is similar to the JSON_DETAILED() function, but it also adds some extra spaces and symbols to the JSON document to make it more pretty and elegant. For example, if we apply the JSON_PRETTY() function to the JSON document stored in @j2, we will get the following output:

{
  "name" : "iPhone 12",
  "price" : 799,
  "specifications" : {
    "color" : "black",
    "size" : "6.1 inches",
    "weight" : "164 grams"
  }
}

We can see that the JSON_PRETTY() function makes the JSON document more pretty and elegant, but also more verbose and less compact.

Conclusion

In this article, we have learned how the JSON_LOOSE() function works in Mariadb, which is a built-in function that adds spaces to a JSON document to make it more readable. We have also seen some examples of how to use this function, and compared it with some related functions, such as JSON_DETAILED(), JSON_COMPACT(), and JSON_PRETTY().