MariaDB JSON Function [With 33 Useful Examples]

In this MariaDB tutorial, we will learn about the basics of MariaDB JSON Functions, and different JSON Functions in MariaDB. Additionally, we will cover the following topics.

• MariaDB JSON Function
• MariaDB Various JSON Function
• MariaDB JSON_ARRAY Function
• MariaDB JSON_ARRAYAGG Function
• MariaDB JSON_ARRAY_APPEND Function
• MariaDB JSON_ARRAY_INSERT Function
• MariaDB JSON_COMPACT Function
• MariaDB JSON_CONTAINS Function
• MariaDB JSON_CONTAINS_PATH Function
• MariaDB JSON_DEPTH Function
• MariaDB JSON_DETAILED Function
• MariaDB JSON_EXIST Function
• MariaDB JSON_EXTRACT Function
• MariaDB JSON_INSERT Function
• MariaDB JSON_KEYS Function
• MariaDB JSON_LENGTH Function
• MariaDB JSON_LOOSE Function
• MariaDB JSON_MERGE Function
• MariaDB JSON_MERGE_PATCH Function
• MariaDB JSON_MERGE_PRESERVE Function
• MariaDB JSON_OBJECT Function
• MariaDB JSON_OBJECTAGG Function
• MariaDB JSON_QUERY Function
• MariaDB JSON_QUOTE Function
• MariaDB JSON_REMOVE Function
• MariaDB JSON_REPLACE Function
• MariaDB JSON_SEARCH Function
• MariaDB JSON_SET Function
• MariaDB JSON_TABLE Function
• MariaDB JSON_TYPE Function
• MariaDB JSON_UNQUOTE Function
• MariaDB JSON_VALID Function
• MariaDB JSON_VALUE Function

MariaDB JSON Function

Here, we will learn about the basics of the MariaDB JSON Function.

In addition to MariaDB, MariaDB provides the user with one more important feature known as MariaDB JSON. The MariaDB JSON function is a standard text format that is used for data interchange as well as unstructured data. The MariaDB JSON function is compatible with pretty much all MariaDB versions.

This MariaDB JSON function has the main advantage that some data are schema-less and some work strictly according to a schema. In such cases, we can match the format of the data. The JSON function of MariaDB helped us to tidy all unstructured data according to our requirements.

A better way to explain the MariaDB JSON function is that it constructs a relation between structured and unstructured data.

Working Of MariaDB JSON Function

Typically, JSON functions work with unstructured data and for data exchange. Structured data is created by naming tables with different attributes, and all attributes are in a well-structured format, meaning all content is suitable for relational data retrieval.

On the other hand, we will have to add other attributes to the table that have a different meaning, and we will consider these attributes as non-relational. MariaDB JSON is therefore a data interpretation system. JSON functions interchange data to match the data type, which includes all attributes in a single

Read: MariaDB vs Postgres

MariaDB Various JSON Function

Here, we will learn about the Various JSON functions in MariaDB.

There are various JSON Functions in MariaDB as shown in the table given below.

S.NO.	NAME OF THE FUNCTION	DESCRIPTION
1.	MariaDB JSON_ARRAY Function	It creates a JSON array
2.	MariaDB JSON_ARRAYAGG Function	Provides a JSON array containing elements for each value in a given set of JSON or SQL values.
3.	MariaDB JSON_ARRAY_APPEND Function	Used to append data into JSON document
4.	MariaDB JSON_ARRAY_INSERT Function	Used to insert into JSON Array
5.	MariaDB JSON_COMPACT Function	Returns a new JSON document that is as short and compact as possible by removing all unnecessary spaces.
6.	MariaDB JSON_CONTAINS Function	Whether JSON document contains a specific object at a specific path.
7.	MariaDB JSON_CONTAINS_PATH Function	Whether JSON documents contain any data on the path
8.	MariaDB JSON_DEPTH Function	Tells the maximum depth of the JSON document
9.	MariaDB JSON_DETAILED Function	It prints the JSON document into a human-readable format
10.	MariaDB JSON_EXIST Function	The function determines whether a value is present at a specified path within the given JSON data.
11.	MariaDB JSON_EXTRACT Function	It is used to return data from the JSON document
12.	MariaDB JSON_INSERT Function	It is used to insert data into the JSON document
13.	MariaDB JSON_KEYS Function	An array of keys from the JSON document
14.	MariaDB JSON_LENGTH Function	It shows the number of elements present in the JSON document
15.	MariaDB JSON_LOOSE Function	Makes it more readable for humans by adding spaces to the JSON document.
16.	MariaDB JSON_MERGE Function	Retaining Duplicate keys while merging JSON documents.
17.	MariaDB JSON_MERGE_PATCH Function	Replaces values of duplicate keys while merging JSON documents.
18.	MariaDB JSON_MERGE_PRESERVE Function	Retaining Duplicate keys while merging JSON documents. Synonym for JSON_MERGE function.
19.	MariaDB JSON_OBJECT Function	It creates a JSON object
20.	MariaDB JSON_OBJECTAGG Function	Based on two arguments, it returns a JSON object containing key-value pairs.
21.	MariaDB JSON_QUERY Function	Based on the path provided, it returns an object or array from the JSON document
22.	MariaDB JSON_QUOTE Function	It quotes the JSON document
23.	MariaDB JSON_REMOVE Function	It removes data from the JSON document
24.	MariaDB JSON_REPLACE Function	It replaces values in the JSON document
25.	MariaDB JSON_SEARCH Function	It tells the path to value within the JSON document
26.	MariaDB JSON_SET Function	It inserts data into the JSON document
27.	MariaDB JSON_TABLE Function	It returns data from the JSON document as a relational table.
28.	MariaDB JSON_TYPE Function	It tells the type of JSON value
29.	MariaDB JSON_UNQUOTE Function	It unquote the JSON value
31.	MariaDB JSON_VALID Function	Tells whether the JSON value is valid
32.	MariaDB JSON_VALUE Function	It extracts value from the JSON documents at the location pointed to by the path provided.

Read: MariaDB Variables Tutorial

MariaDB JSON_ARRAY Function

Here, we will learn about MariaDB JSON_ARRAY Function, its syntax with an example.

MariaDB JSON_ARRAY is a built-in function that returns a JSON array that contains specified values, provided as arguments.

The syntax of MariaDB JSON_ARRAY is given below.

JSON_ARRAY([value[, value2] ...])

Here, value, value2 becomes the elements of the resulting array. They are optional, so it can be possible to create an empty array by passing zero arguments.

Examples demonstrating the working of the function are given below.

Example 1 – MariaDB JSON_ARRAY on strings.

SELECT JSON_ARRAY('USA', 'CANADA', 'UK');

Here, we are providing strings as elements of the JSON Array.

Example 2 – MariaDB JSON_ARRAY on numbers and null.

SELECT JSON_ARRAY(0, 1, 2, 3, null);

Here, we are providing numbers and null as elements of the JSON Array.

Example 3 – MariaDB JSON_ARRAY on Escape Characters.

SELECT JSON_ARRAY('MariaDB "JSON" Function');

Here, we are inserting a string in a JSON array in which we have double quotes. It automatically escapes double quotes with a backslash.

Example 4 – MariaDB JSON_ARRAY on Empty Strings.

SELECT JSON_ARRAY('','','');

Here, we are providing empty strings as elements of the JSON Array by typing single quotes.

Example 5 – MariaDB JSON_ARRAY on Empty Array by passing zero arguments.

SELECT JSON_ARRAY();

Here, we are creating an empty array by not passing any arguments.

Example 6 – MariaDB JSON_ARRAY on a table in a database.

We are using the cities table which is shown below.

SELECT * FROM cities;

SELECT JSON_ARRAY(cname) from cities;

Here, we are passing a column of a table i.e. cname as arguments of the JSON_ARRAY function. So, the list of cities will be stored as the elements of the JSON Array.

Here, we have learned about MariaDB JSON_ARRAY Function, its syntax with an example.

Read: MariaDB Logs – Helpful Guide

MariaDB JSON_ARRAYAGG Function

Here, we will learn about MariaDB JSON_ARRAYAGG Function, its syntax with an example.

The MariaDB JSON_ARRAYAGG function returns the JSON array from an expression or a column. This means when the JSON_ARRAYAGG function runs on a specific column, then it returns the JSON array containing the column values.

The syntax of MariaDB JSON_ARRAYAGG is given below.

JSON_ARRAYAGG( DISTINCT  expression
             ORDER B col_name | expr}
                           ASC | DESC
             LIMIT

Here:

• DISTINCT – To remove the duplicate values from the array.
• ORDER BY – To specify a particular order to the elements of the array.
• LIMIT – To specify a particular number of elements in the array.

Examples demonstrating the working of the MariaDB JSON_ARRAYAGG function are given below.

Example 1 – MariaDB JSON_ARRAYAGG on Table.

SELECT JSON_ARRAYAGG(City_Name)
FROM US_Places;

Here, we are passing a column of a table i.e. City_Name as arguments of the function JSON_ARRAYAGG. So, the list of cities will be stored as the elements of the JSON Array.

Example 2 – MariaDB JSON_ARRAYAGG with Distinct Clause on Table.

SELECT JSON_ARRAYAGG(DISTINCT City_Name)
FROM US_Places;

Here, we are passing a column of a table i.e. City_Name as arguments of the function JSON_ARRAYAGG along with the DISTINCT clause. So, the list of cities with no duplicate values will be stored as the elements of the JSON Array.

Here, you can see that The "United States Of America" is now present only once in the list of elements only because of the DISTINCT clause.

Example 3 – MariaDB JSON_ARRAYAGG with ORDER BY clause on Table.

SELECT JSON_ARRAYAGG(City_Name ORDER BY City_Name DESC)
FROM US_Places;

Here, we are passing a column of a table i.e. City_Name as arguments of the function JSON_ARRAYAGG along with the ORDER BY clause. So, the list of cities in a particular order i.e. Descending Order will be stored as the elements of the JSON Array.

Example 4 – MariaDB JSON_ARRAYAGG with the LIMIT clause on Table.

SELECT JSON_ARRAYAGG(City_Name LIMIT 2)
FROM US_Places;

Here, we are passing a column of a table i.e. City_Name as arguments of the function JSON_ARRAYAGG along with the LIMIT clause. So, the list of cities consisting of only two names of places will be stored as the elements of the JSON Array.

Here, we can see that there are only two elements in the array.

Example 5 – MariaDB JSON_ARRAYAGG to group the results.

SELECT 
    ID,
    JSON_ARRAYAGG(City_Name)
FROM US_Places
GROUP BY ID;

Here, we are grouping the rows by ID from US_Places and displaying the ID and City_Name stored inside a JSON Array.

Here, we have learned about MariaDB JSON_ARRAYAGG Function, its syntax with an example.

Read: MariaDB Foreign Key + Examples

MariaDB JSON_ARRAY_APPEND Function

Here, we will learn about MariaDB JSON_ARRAY_APPEND Function, its syntax with an example.

The MariaDB JSON_ARRAY_APPEND function is a built-in function that adds or appends the value at the end of a JSON array that JSON document contains, and it generates the result with appended value.

The syntax of MariaDB JSON_ARRAY_APPEND is given below.

JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)

Here:

• json_doc – JSON document in ehich values are to be added.
• path – Path where we need to append the values.
• value – Values that we need to append.

Examples demonstrating the working of the MariaDB JSON_ARRAY_APPEND function are given below.

Example 1 – MariaDB JSON_ARRAY_APPEND on JSON Document.

SET @US_doc = '["New York", "California", "New Jersey", "Los Angeles"]';

SELECT JSON_ARRAY_APPEND(@US_doc, '$', 'Texas');

Here, we are creating the JSON Document i.e. US_doc with some of the values i.e. "New York", "California", "New Jersey", "Los Angeles" and appending a single value to the end of the JSON document i.e. "Texas".

Example 2 – MariaDB JSON_ARRAY_APPEND Multiple Values.

SELECT JSON_ARRAY_APPEND(@US_doc, '$', 'Chicago', '$', 'San Francisco');

Here, we are appending two values i.e. 'Chicago' and 'San Francisco' to the end of the JSON document i.e. US_doc.

Here, you can see that 'Chicago' and 'San Francisco' have been added to the end of the JSON document.

Example 3 – MariaDB JSON_ARRAY_APPEND on more than one array.

SET @Place_doc = '{"City": ["Los Angeles", "Dallas"], "State": ["California", "Texas"]}';

SELECT JSON_ARRAY_APPEND(@json_doc, '$.City', 'Tucson', '$.State', 'Arizona');

Here, we are creating the JSON Document i.e. Places_doc with some of the arrays i.e. City and State with corresponding values stored in them i.e. "Los Angeles", "Dallas", "California", and "Texas" and appending a single value at the end of each of the JSON arrays i.e. 'Tucson' and 'Arizona' present in JSON document i.e. Places.

Here, we have learned about MariaDB JSON_ARRAY_APPEND Function, its syntax with an example.

Read: MariaDB Temporary Table

MariaDB JSON_ARRAY_INSERT Function

Here, we will learn about MariaDB JSON_ARRAY_INSERT Function, its syntax with an example.

The MariaDB JSON_ARRAY_INSERT function is a built-in function that inserts the value/values in the JSON document and generates the result.

The syntax of MariaDB JSON_ARRAY_INSERT is given below.

JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)

Here:

• json_doc – JSON document in ehich values are to be inserted.
• path – Path where we need to insert the value/values.
• value – Value/values that we need to insert.

Examples demonstrating the working of the MariaDB JSON_ARRAY_INSERT function are given below.

Example 1 – MariaDB JSON_ARRAY_INSERT on JSON Document.

SELECT JSON_ARRAY_APPEND(@US_doc, '$[2]', 'New Orleans');

Here, we are creating the new JSON Document i.e. US_doc with some of the values i.e. " New York", "California", "New Jersey", "Los Angeles", "Texas" and inserting a single value to the end of the JSON document i.e. "New Orleans".

Example 2 – MariaDB JSON_ARRAY_INSERT Multiple Values.

SELECT JSON_ARRAY_INSERT(@US_doc, '$[0]', 'Chicago', '$[2]', 'San Francisco');

Here, we are inserting two values i.e. 'Chicago' and 'San Francisco' in the JSON document i.e. US_doc.

Here, you can see that 'Chicago' and 'San Francisco' have been added to the JSON document.

Example 3 – MariaDB JSON_ARRAY_INSERT on more than one array.

SET @Place_doc = '{"City": ["Los Angeles", "Dallas"], "State": ["California", "Texas"]}';

SELECT JSON_ARRAY_INSERT(@Place_doc, '$.City[0]', 'Cincinnati', '$.State[0]', 'Ohio');

Here, we are creating the new JSON Document i.e. Places_doc with some of the arrays i.e. City and State with corresponding values stored in them i.e. "Los Angeles", "Dallas", "California", and "Texas" and appending a single value at the end of each of the JSON arrays i.e. 'Cincinnati' and 'Ohio' present in JSON document i.e. Places.doc.

Here, we have learned about MariaDB JSON_ARRAY_INSERT Function, its syntax with an example.

Read: MariaDB Reset Root Password

MariaDB JSON_COMPACT Function

Here, we will learn about MariaDB JSON_COMPACT Function, its syntax with an example.

MariaDB has a function called JSON_COMPACT() that compacts JSON documents by removing unnecessary spaces and presents the result as a compact document.

This is useful when storing JSON data in a database column with a low space requirement, or when you don’t want JSON documents to consume excessive space.

Alternatively, use the JSON_DETAILED() function to spread the JSON document over multiple lines whose functionality is just opposite to JSON_DETAILED() function. You can learn about JSON_DETAILED() function in this post later.

The syntax of MariaDB JSON_COMPACT is given below.

JSON_COMPACT(json_doc)

Here:

• json_doc – JSON document which is going to be compact by removing unnecessary spaces.

An example demonstrating the working of the MariaDB JSON_COMPACT function on the JSON document is given below.

Here, we are using JSON Document i.e. Place_doc in this example which has been shown below.

SELECT 
    JSON_COMPACT(@Place_doc) AS Compact;

SELECT
    @Place_doc AS Original;

Here, we are using the JSON document i.e. Place_doc, and displaying the original and compact version of the JSON document by using the JSON_COMPACT Function.

Here, there is a compacted version and the original JSON document before it was compacted.

Here, we have learned about MariaDB JSON_COMPACT Function, its syntax with an example.

MariaDB JSON_CONTAINS Function

Here, we will learn about MariaDB JSON_CONTAINS Function, its syntax with an example.

MariaDB has a function called JSON_CONTAINS() which is a built-in feature of MariaDB that allows you to determine whether a specified value is contained within the JSON document, or whether it is found within a specific path.

The MariaDB JSON_CONTAINS function returns 1 if the value is contained, 0 otherwise, and NULL if any of the arguments are NULL.

The syntax of MariaDB JSON_CONATINS is given below.

JSON_CONTAINS(json_doc, val[, path])

Here:

• json_doc – JSON document in which we are going to search for a particular value.
• path – It is optional, It is the path of node within JSON document where value exist or not that we want to find.
• value – Value/values that we need to find.

An example demonstrating the working of the MariaDB JSON_CONTAINS function on the JSON document is given below.

SELECT JSON_CONTAINS(@US_doc, 'Las Vegas');

Here, we are searching for 'Las Vagas' in the US_doc JSON document using the JSON_CONTAINS function in MariaDB.

Here, we have learned about MariaDB JSON_CONTAINS Function, its syntax with an example.

Read: MariaDB Backup Database

MariaDB JSON_CONTAINS_PATH Function

Here, we will learn about MariaDB JSON_CONTAINS_PATH Function, its syntax with an example.

MariaDB has a function called JSON_CONTAINS_PATH() which is a built-in feature of MariaDB that allows you to determine whether a specified value is contained within the JSON document is found within a specified path.

The MariaDB JSON_CONTAINS_PATH function returns 1 if the value is contained, 0 otherwise, and NULL if any of the arguments are NULL.

The syntax of MariaDB JSON_CONATINS_PATH is given below.

JSON_CONTAINS_PATH(json_doc, return_arg, path[, path] ...)

Here:

• json_doc – JSON document in which we are going to search for a particular value.
• path – It specifies the path where we need to find the data.
• return_arg – It determines how multiple paths are handled. The value of return_arg can be one or all.
  • one – If one or more paths are found within the JSON document, the function returns 1.
  • all – if all paths are present in the JSON document, all will return 1.

An example demonstrating the working of the MariaDB JSON_CONTAINS_PATH function on the JSON document is given below.

SELECT JSON_CONTAINS_PATH(@Place_doc, 'one', '$.City')

Here, we are passing Place_doc as the JSON document in which we need to search, and '$.City' is the path.

Here, we can see that the result for the above query i.e. ‘1‘ means that the path exists.

Here, we have learned about MariaDB JSON_CONTAINS_PATH Function, its syntax with an example.

Read: MariaDB Enable Remote Access

MariaDB JSON_DEPTH Function

Here, we will learn about MariaDB JSON_DEPTH Function, its syntax with an example.

MariaDB has a built-in function called JSON_DEPTH() which lets you determine how deep the JSON document goes.

In addition, it returns the depth of the JSON document when the JSON document is provided as an argument.

The syntax of MariaDB JSON_DEPTH is given below.

JSON_DEPTH(json_doc)

Here:

• json_doc – JSON document in which we are going to get the depth.

An example demonstrating the working of the MariaDB JSON_DEPTH function on the JSON document is given below.

SELECT JSON_DEPTH(@Place_doc);

Here, we are passing Place_doc as the JSON document in which we need to find the depth.

Here, we have learned about MariaDB JSON_DEPTH Function, its syntax with an example.

Read: MariaDB Rename Column

MariaDB JSON_DETAILED Function

Here, we will learn about MariaDB JSON_DETAILED Function, its syntax with an example.

MariaDB has a function called JSON_DETAILED() which is a built-in feature of MariaDB that takes the JSON document returns a more readable format for humans.

This process is sometimes known as prettifying a document. Essentially, it’s the same as MySQL’s JSON_PRETTY() function.

The JSON_COMPACT() function is used for the opposite effect (i.e. to compress the JSON document). You can learn about this from the above topic i.e. MariaDB JSON_COMPACT function.

The syntax of MariaDB JSON_DETAILED is given below.

JSON_DETAILED(json_doc[, tab_size])

Here:

• json_doc – It is JSON document.
• tab_size – It is optional that specifies the size of the table/indents.

An example demonstrating the working of the MariaDB JSON_DETAILED function on the JSON document is given below.

SELECT JSON_DETAILED(@Place_doc) AS Detailed;

Here, we have learned about MariaDB JSON_DETAILED Function, its syntax with an example.

Read: MariaDB Transaction – Helpful Guide

MariaDB JSON_EXIST Function

Here, we will learn about MariaDB JSON_EXIST Function, its syntax with an example.

MariaDB has a function called JSON_EXIST() function which can determine whether a specified value exists in the JSON document for a specified path.

As an argument, the function accepts the JSON document that contains the path and returns 1 if the path is found. Although it does not appear to check for a given value, the function appears to be fairly intuitive.

The syntax of MariaDB JSON_EXIST is given below.

JSON_EXISTS(json_doc, path)

Here:

• json_doc – JSON document in which we are going to search for a particular value.
• path – It specifies the path where we need to find the data.

An example demonstrating the working of the MariaDB JSON_EXISTS function on the JSON document is given below.

SELECT JSON_EXISTS(@US_data, '$.city');

Here, we are passing Place_doc as the JSON document in which we need to search, and '$.City' is the path.

Here, we can see that the result for the above query i.e. ‘1‘ means that the path exists.

Here, we have learned about MariaDB JSON_EXISTS Function, its syntax with an example.

Read: MariaDB Select Statement

MariaDB JSON_EXTRACT Function

Here, we will learn about MariaDB JSON_EXTRACT Function, its syntax with an example.

MariaDB has a function called JSON_EXTRACT() which is a built-in feature of MariaDB that extracts the data from the JSON document on a given path.

This returns both single and multiple values just as the number of matches in an array. If a single value is matching, then only a single value is returned and in the case of multiple value matching, multiple values are returned.

The syntax of MariaDB JSON_EXTRACT is given below.

JSON_EXTRACT(json_doc, path[, path] ...)

Here:

• json_doc – It is JSON document.
• tab_size – It is optional that specifies the path in the JSON document.

An example demonstrating the working of the MariaDB JSON_EXTRACT function on the JSON document is given below.

SET @US_data = '
    { 
        "city": "Los Angeles", 
        "state": "California", 
        "country": "USA" 
    }
';
SELECT JSON_EXTRACT(@US_data, '$.state');

Here, we have created the JSON document consisting of city, state, and country and corresponding values i.e. Los Angeles, California, and the USA. And fetching the same data using the JSON_EXTRACT function.

Here, we have learned about MariaDB JSON_EXTRACT Function, its syntax with an example.

Read: MariaDB Set Variable – Complete Guide

MariaDB JSON_INSERT Function

Here, we will learn about MariaDB JSON_INSERT Function, its syntax with an example.

MariaDB has a function called JSON_INSERT() which is a built-in feature of MariaDB that inserts data into the JSON document and outputs the resulting JSON document with inserted value.

The syntax of MariaDB JSON_INSERT is given below.

JSON_INSERT(json_doc, path, val[, path, val] ...)

Here:

• json_doc – It is JSON document.
• tab_size – It is optional that specifies the path in the JSON document.
• val – It is the value that is inserted.

An example demonstrating the working of the MariaDB JSON_INSERT function on the JSON document is given below.

SELECT JSON_INSERT(@US_data, '$.year', 2006);

Here, we have already created the JSON document consisting of city, state, and country and corresponding values i.e. Los Angeles, California, and the USA. And inserting the data i.e. "year": 2006 using the JSON_INSERT function.

Here, we have learned about MariaDB JSON_INSERT Function, its syntax with an example.

MariaDB JSON_KEYS Function

Here, we will learn about MariaDB JSON_KEYS Function, its syntax with an example.

The MariaDB JSON_KEYS Function extracts the keys as JSON array from the JSON object and these keys are extracted from only the top-level of the JSON object.

The syntax of MariaDB JSON_KEYS is given below.

JSON_KEYS(json_doc[, path])

Here:

• json_doc – JSON document which is going to take more space by adding unnecessary spaces.
• path – It is path of level from which keys is extracted like from top-level or nested-level, this is optional.

An example demonstrating the working of the MariaDB JSON_KEYS function on the JSON document is given below.

Here, we are using JSON Document i.e. in this example which has been shown below.

SELECT JSON_KEYS('{"USA":{"New York": 1}, "United Kindom": {"London": 1}}');

Here, we have provided a JSON object that contains state and its city with rank consisting of keys USA and United Kindom with corresponding values "New York": 1 and "London": 1 that is also a key-value pair and called nested JSON.

From the output, the keys of the top-level JSON object are USA and United Kindom, but how can get the keys other than a top-level JSON object, for that we will use the optional option that is the path.

SELECT JSON_KEYS('{"USA":{"New York": 1}, "United Kindom": {"London": 1}}','$.United Kindom');

Here, we have provided the same keys and values with an extra option that is path $.United Kindom which means we need the keys from this location. We can access any key-value pair of JSON objects by specifying path as $.keys_name.

From the output at the specified location, the key is London with another key which is United Kindom.

Here, we have learned about MariaDB JSON_KEYS Function, its syntax with an example.

Read: MariaDB on Duplicate Key Update

MariaDB JSON_LENGTH Function

Here, we will learn about MariaDB JSON_LENGTH Function, its syntax with an example.

JSON_LENGTH function in MariaDB is an in-built function that returns the length of the JSON document in MariaDB.

During the call of the JSON_LENGTH function, we need to provide the JSON document as an argument. We can provide the path as an argument in the JSON document to return the length of a value inside the JSON document.

The length can be calculated as shown below.

• The length of a scalar is always 1.
• In case of an array, it can be calculated by the number of elements in the array.
• In case of an object, it can be calculated by the number of members in the object.

The syntax of MariaDB JSON_LENGTH is given below.

JSON_LENGTH(json_doc[, path])

Here:

• json_doc – JSON document which we are going to find the length.
• path – It is optional argument which apecifies the path inside the JSON document.

An example demonstrating the working of the MariaDB JSON_LENGTH function on the JSON document is given below.

Here, we are using JSON Document i.e. US_data in this example which has been shown below.

SET @US_data = '
    { 
        "city": "Los Angeles", 
        "state": "California", 
        "country": "USA" 
    }
';

SELECT JSON_LENGTH(@US_data);

Here, we have created the JSON document i.e. US_data as an argument consisting of city, state, and country, and corresponding values i.e. Los Angeles, California, and the USA and calculated its length.

Here, we have learned about MariaDB JSON_LENGTH Function, its syntax with an example.

Read: MariaDB Window functions

MariaDB JSON_LOOSE Function

Here, we will learn about MariaDB JSON_LOOSE Function, its syntax with an example.

MariaDB has a function called JSON_LOOSE that adds spaces to JSON documents to make them more readable.

For using the opposite effect, use the JSON_COMPACT function.

The syntax of MariaDB JSON_LOOSE is given below.

JSON_LOOSE(json_doc)

Here:

• json_doc – JSON document which is going to take more space by adding unnecessary spaces.

An example demonstrating the working of the MariaDB JSON_LOOSE function on the JSON document is given below.

Here, we are using JSON Document i.e. in this example which has been shown below.

SELECT JSON_LOOSE(@US_data);

Here, we have created the JSON document i.e. US_data consisting of city, state, and country, and corresponding values i.e. Los Angeles, California,and theUSA, to use as an argument in the JSON_LOOSE function in MariaDB.

Here, there is a loose version and the original JSON document before it was compacted.

Here, we have learned about MariaDB JSON_LOOSE Function, its syntax with an example.

Read: MariaDB Row_Number Tutorial

MariaDB JSON_MERGE Function

Here, we will learn about MariaDB JSON_MERGE Function, its syntax with an example.

The MariaDB JSON_MERGE merges the different JSON documents into one result, if any of the documents is null, then returns the null.

The syntax of MariaDB JSON_MERGE is given below.

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

Here:

• json_doc – JSON document that will be merged with each other.

An example demonstrating the working of the MariaDB JSON_MERGE function on JSON documents is given below.

SET @USA = '["New_York", "Chicago"]';
SET @United_Kindom = '["London", "Leeds"]';

Here, we have created the two JSON document @USA and @United_Kindom with values '["New_York", "Chicago"]' and '["London", "Leeds"]' using the SET statement.

SELECT JSON_MERGE(@USA,@United_Kindom);

Here, we have provided the two JSON documents to a function JSON_MERGE( ) to merge these documents.

From the output, we can see that the result contains the all values of two JSON documents into one result as ["New_York", "Chicago", "London", "Leeds"].

Now, we are going to merge the JSON document with the null argument i.e. in this example which has been shown below.

SET @USA = '["New_York", "Chicago"]';
SET @United_Kindom = '';

SELECT JSON_MERGE(@USA,@United_Kindom);

Here, Every code is the same as we have done above example but provided the second JSON document @United_Kindom with null values and then merged documents using JSON_MERGE function.

From the output, we have concluded that while merging the JSON documents, if any of the documents is null, then JSON_MERGE the function returns the result as NULL.

Here, we have learned about MariaDB JSON_MERGE Function, its syntax with an example.

Read: MariaDB DateTime Tutorial

MariaDB JSON_MERGE_PATCH Function

Here, we will learn about MariaDB JSON_MERGE_PATCH Function, its syntax with an example.

The MariaDB JSON_MERGE_PATCH is a replacement for JSON_MERGE that also merges the JSON documents into a single result as an array. It returns the result without duplicate values.

MariaDB JSON_MERGE_PATCH is replacement of JSON_MERGE.

The syntax of MariaDB JSON_MERGE_PATCH is given below.

JSON_MERGE_PATCH(json_doc)

Here:

• json_doc – It is JSON document that contains JSON object or array.

An example demonstrating the working of the MariaDB JSON_MERGE_PATCH function on the JSON document is given below.

Here, we are going to create a JSON variable containing JSON data i.e. @counting_value1 and @counting_value2 and passing this variable to a function JSON_MERGE_PATCH in this example which has been shown below.

SET @counting_value1 = '[3, 4]';
SET @counting_value2 = '[4, 5]';

Pass the above variables to function.

SELECT JSON_MERGE_PATCH(@counting_value1,@counting_value2);

Here, we have learned about MariaDB JSON_MERGE_PATCH Function, its syntax with an example.

Read: MariaDB DATEDIFF Function

MariaDB JSON_MERGE_PRESERVE Function

Here, we will learn about MariaDB JSON_MERGE_PRESERVE Function, its syntax with an example.

The MariaDB JSON_MERGE_PRESERVE is also a new version of JSON_MERGE and merges the JSON documents into a single result as an array with duplicate values.

The syntax of MariaDB JSON_MERGE_PRESERVE is given below.

JSON_MERGE_PRESERVE(json_doc, json_doc,....)

Here:

• json_doc – JSON document that will be merged with each other into a single result.

An example demonstrating the working of the MariaDB JSON_MERGE_PRESERVE function on the JSON document is given below.

SET @USA = '["New_York", "Chicago"]';
SET @United_Kindom = '["London", "Leeds"]';

Here, we have created the two JSON document @USA and @United_Kindom with values '["New_York", "Chicago"]' and '["London", "Leeds"]' using the SET statement.

SELECT JSON_MERGE(@USA,@United_Kindom);

Here, we have provided the two JSON documents to a function JSON_MERGE( ) to merge these documents.

From the output, we can see that the result contains the all values of two JSON documents into one result as ["New_York", "Chicago", "London", "Leeds"]. As we talked about before that JSON_MERGE_PRESERVE is the new version of the JSON_MERGE function then it returns the same result.

Here, we have learned about MariaDB JSON_MERGE_PRESERVE Function, its syntax with an example.

Read: MariaDB AUTO_INCREMENT + Examples

MariaDB JSON_OBJECT Function

Here, we will learn about MariaDB JSON_OBJECT Function, its syntax with an example.

The MariaDB JSON_OBJECT generates the JSON object as key-value pair within curly braces like {key: value} from the provided values.

The syntax of MariaDB JSON_OBJECT is given below.

JSON_OBJECT([key, value, ...])

Here:

• [key, value, …] – Here values are provided in format like key and value to generate the JSON object.

An example demonstrating the working of the MariaDB JSON_OBJECT function on the JSON document is given below.

Here, we are providing a series of values to a function JSON_OBJECT i.e. in this example which has been shown below.

SELECT JSON_OBJECT("United State","New York","United Kindom","London","Canada","Toronto");

Here, we have provided the series of values "United State","New York","United Kindom","London","Canada","Toronto" where the first value act as a key and the second value as value to that key, this pattern is followed for all the values provided to a function.

From the output, we can see how series of values provided to a function JSON_OBJECT returned the JSON object as key-value pair within curly braces {"United State": "New York", "United Kindom": "London", "Canada": "Toronto"}.

Here, we have learned about MariaDB JSON_OBJECT Function, its syntax with an example.

Read: MariaDB Delete Row + Examples

MariaDB JSON_OBJECTAGG Function

Here, we will learn about MariaDB JSON_OBJECTAGG Function, its syntax with an example.

The MariaDB JSON_OBJECTAGG accepts two expressions or columns names and returns the JSON object containing key-value pair.

The syntax of MariaDB JSON_OBJECTAGG is given below.

JSON_OBJECTAGG(key, value)

Here:

• key,value: It can be any two expression or column names

First, create a new table name State_City that will contain the state name and city name of that state.

CREATE TABLE Country_City(country_name VARCHAR(40),city_name VARCHAR(40));

Here in the above code, we are creating tables as Country_City.

• CREATE TABLE: It is the command to create a new table in MariaDB.
• country_name VARCHAR(40): It is a column of type characters to store the name of the country.
• city_name VARCHAR(40): It is a column of type characters to store the name of the city in that country.

Insert the following four records into a table.

INSERT INTO Country_City(country_name,city_name)VALUES
('United State','New York'),('Canada','Toronto'),
('United Kindom','London'),('Mexico','Tijuana');

View the table data.

SELECT * FROM Country_City;

An example demonstrating the working of the MariaDB JSON_OBJECTAGG function on the JSON document is given below.

Here, we are going to provide two column names to a function JSON_OBJECTAGG i.e. in this example which has been shown below.

SELECT JSON_OBJECTAGG(country_name, city_name) FROM Country_City;

Here, we have passed the two-column names country_name and city_name to function JSON_OBJECTAGG to convert these columns values into a single result containing key-value pair.

From the output, we can see that the two-column value converted into a single result as a JSON object {"United State":"New York", "Canada":"Toronto", "United Kindom":"London", "Mexico":"Tijuana"}.

Here, we have learned about MariaDB JSON_OBJECTAGG Function, its syntax with an example.

Read: MariaDB Full Outer Join

MariaDB JSON_QUERY Function

Here, we will learn about MariaDB JSON_QUERY Function, its syntax with an example.

The MariaDB JSON_QUERY returns the object from the JSON document based on the provided path.

The syntax of MariaDB JSON_QUERY is given below.

JSON_QUERY(json_doc, path)

Here:

• json_doc – JSON document that contaisn the key-value pair within curly braces.
• path – It define path from which object or array is generated.

An example demonstrating the working of the MariaDB JSON_QUERY function on the JSON document is given below.

Here, we are going to pass JSON Document to function i.e. in this example which has been shown below.

SET @country = '{"USA":{"New York":1, "Los Angeles":[2]}}';
SELECT JSON_QUERY(@country, '$.USA');SELECT JSON_QUERY('{"USA":{"New York":1, "Los Angeles":[2]}}', '$.USA');

Here, we have created a variable @country as the JSON document '{"USA":{"New York":1, "Los Angeles":[2]}}' to store the city ranking of the country USA.

Then passed this variable to function JSON_QUERY with the path '$.USA' to get the array or object from that path.

From the output, a result is a JSON object from the specified path.

Here, we have learned about MariaDB JSON_QUERY Function, its syntax with an example.

Read: MariaDB Cast with Examples

MariaDB JSON_QUOTE Function

Here, we will learn about MariaDB JSON_QUOTE Function, its syntax with an example.

The MariaDB JSON_QUTOE is used to quote the provided string value with special characters, double characters, etc as JSON value or valid JSON string literals. So that it can be used in JSON documents.

The syntax of MariaDB JSON_QUOTE is given below.

JSON_QUOTE(json_value)

Here:

• json_value – It can be any value as string that we want to quote.

An example demonstrating the working of the MariaDB JSON_QUOTE function on a JSON value is given below.

Here, we are going to use the same table that we have used in the above sub-section i.e. Country_City in this example which has been shown below.

SELECT JSON_QUOTE(country_name) FROM country_city;

Here, we have passed the column country_name of the table Country_City to a function JSON_QUOTE, the function wraps all the values of the column into the double quote.

From the output, all the values with column have wrapped into the double quote as we can see "United State", "Canada", "United Kindom" and "Mexico".

Here, we have learned about MariaDB JSON_QUOTE Function, its syntax with an example.

Read: MariaDB Substring [11 Examples]

MariaDB JSON_REMOVE Function

Here, we will learn about MariaDB JSON_REMOVE Function, its syntax with an example.

The JSON_REMOVE function deletes the key-value pair from the JSON document just as in the specified path.

The syntax of MariaDB JSON_REMOVE is given below.

JSON_REMOVE(json_doc, path)

Here:

• json_doc – It is JSON document that contains the data in key-value pair form whose data we want to remove.
• path – It is path or place from which data must be deleted.

An example demonstrating the working of the MariaDB JSON_REMOVE function on the JSON document is given below.

Here, we are going to create a table City_Ranking with a column country_name that contains JSON Document and remove some data from this document in this example which has been shown below.

CREATE TABLE City_Ranking(country_name JSON CHECK(JSON_VALID(country_name)));

Insert the following records in that table.

INSERT INTO City_Ranking(country_name)VALUE
('{"USA":{"New York":1, "Los Angeles":2}}'),
('{"United Kindom":{"London":1, "Greater Manchester":2}}'),
('{"Canada":{"Toronto":1, "Montreal":2}}'),
('{"New Zealand":{"Auckland":1, "Wellington":2}}');

View the table using the below query.

SELECT * FROM City_Ranking;

Let’s say want to remove the data USA with its city ranking from the above table, run the below query to remove the data from JSON data.

SELECT JSON_REMOVE(country_name,'$.USA') FROM City_Ranking;

Here, we have passed the column country_name of the table City_Ranking with path '$.USA' to a function JSON_REMOVE. The function finds the keys USA with value and remove it from the column which is shown in the below output.

Here, we have learned about MariaDB JSON_REMOVE Function, its syntax with an example.

Read: MariaDB Insert Into + Examples

MariaDB JSON_REPLACE Function

Here, we will learn about MariaDB JSON_REPLACE Function, its syntax with an example.

The MariaDB JSON_REPLACE the value of keys with another value within JSON document by specified path.

The syntax of MariaDB JSON_REPLACE is given below.

JSON_REPLACE(json_doc, path, value)

Here:

• json_doc – It is the JSON document conatining JSON data as key-value pair.
• path – It is used to specify the path of the data whose value we want to replace withing JSON document.
• value – It is value that replaces the old value of keys.

An example demonstrating the working of the MariaDB JSON_REPLACE function on a table containing JSON data is given below.

Here, we are going to use the same table that we have used in the above sub-section i.e. City_Ranking in this example which has been shown below.

SELECT JSON_REPLACE(country_name,'$.USA.Los Angeles',3) FROM City_Ranking;

Here, we have passed the column country_name of table City_Ranking with path '$.USA.Los Angeles' and value as 4 to a function JSON_REPLACE.

The function finds the key Los Angeles of key USA with value and replace the Los Angeles value with 3 in the column containing JSON data which is shown in the below output.

From the output, the value of key Los Angeles has replaced to 3.

Here, we have learned about MariaDB JSON_REPLACE Function, its syntax with an example.

Read: How to Change Column in MariaDB

MariaDB JSON_SEARCH Function

Here, we will learn about MariaDB JSON_SEARCH Function, its syntax with an example.

The MariaDB JSON_SEARCH function returns the path of the string that exists within the JSON document. This means if we want to find the path of the key-value pair within the JSON document, then by providing any value of the key to this function, we can find the path.

The syntax of MariaDB JSON_SEARCH is given below.

JSON_SEARCH(json_doc, return_argument, search_string)

Here:

• json_doc – It is JSON document containing the key-value pair.
• return_argument – It is used to specify that which path string we want like only one matching path or all the matching path string withou duplicate path string. It accepts two value that is one which return only first match path string and all which returns the multiple path string without duplicate path string as an array.
• search_string- It is the string value whose path we want to know or find.

Here, we are going to use the same table that we have used in the above sub-section i.e. City_Ranking in this example which has been shown below.

SELECT JSON_SEARCH(country_name, 'one', '1') FROM City_Ranking;

Here, we have passed the column country_name of table City_Ranking with return_argument 'one' and string value to search as 1 to a function JSON_SEARCH.

The function finds the path of the string value 1 and return the one path string where it matches in a column for each JSON document or value which is shown in the below output.

From the above output, we can see the path string of each JSON document in column country_name.

Here, we have learned about MariaDB JSON_SEARCH Function, its syntax with an example.

MariaDB JSON_SET Function

Here, we will learn about MariaDB JSON_SET Function, its syntax with an example.

The MariaDB JSON_SET function is used to update the value of existing keys and insert the new key-value pair within the JSON document.

The syntax of MariaDB JSON_SET is given below.

JSON_SET(json_doc, path, value)

Here:

• json_doc – It is JSON document that contains the key-value pair.
• path – It is the path for which keys we want to update value or insert the new key-value pair.
• value – It is the new value for keys.

An example demonstrating the working of the MariaDB JSON_SET function on a table containing JSON documents is given below.

Here, we are going to use the same table that we have used in the above sub-section i.e. City_Ranking in this example which has been shown below.

SELECT JSON_SET(country_name, '$.United Kindom.London', '3') FROM City_Ranking;

Here, we have passed the column country_name of table City_Ranking with path '$.United Kindom.London' and value as 4 to a function JSON_SET.

The function finds the key London of key United Kindom with value and set the new value of key London as 3 in the column containing JSON data which is shown in the below output.

Here, we have learned about MariaDB JSON_SET Function, its syntax with an example.

Read: MariaDB Update Statement

MariaDB JSON_TABLE Function

Here, we will learn about MariaDB JSON_TABLE Function, its syntax with an example.

The MariaDB JSON_TABLE returns a table from a given JSON document which means it generates the relation form by extracting the data from a given JSON document.

The syntax of MariaDB JSON_TABLE is given below.

JSON_TABLE(json_document, Context_Path COLUMNS (column_list ...)
)as alias

Here:

• json_document – It is JSON document from which data is extracted to create a relation form.
• Context_path – It is a expression and act as source of rows that points to a group of nodes within JSON document.
• COLUMNS – It is used to specify the column name and type that JSON_TABLE is going to return.

An example demonstrating the working of the MariaDB JSON_TABLE function on the JSON document based on different column definitions is given below.

There are four types of column definitions used to generate the relation form.

1. Path Columns

Path columns: The syntax is given below.

name datatype PATH path_string (on_empty) (on_error)

Where,

• name: It is the name of the column.
• datatype: It is the data type of the column.
• PATH: It is the keyword to mention the path of the node in the JSON document to extract the value of that node.
• path_string: It is a path ($.key_name) that point to a node in the JSON document.
• (on_empty) (on_error) : It is used to deal with error conditon when value doesn’t exist.

Here, we are going to create a table Restaurant_Employee with a column emp_name and position of data type varchar from the JSON document which has been shown below.

Create a new JSON document as restaurant.

SET @restaurant = '[
  {"name":"Jhon", "job":"Kitchen_Staff"},
  {"name":"Paul",  "job":"Manager"}
]';

Now generate the relation form from the JSON document using the JSON_TABLE function.

SELECT * FROM json_table(@restaurant,'$[*]'
COLUMNS(
       emp_name VARCHAR(30) path '$.name',
       emp_position VARCHAR(30) path '$.job')
) AS Restaurant_Employee;

2. ORDINALITY Columns

ORDINALITY Columns: It is used to count the rows that start from 1. The syntax is given below.

name FOR ORDINALITY

Where,

• name: It is the name of the row.
• FOR ORDINALITY: It defines the counting number for each row that start from 1.

Here, we are going to generate the same table Restaurant_Employee with an additional column id of data type integer from the same JSON document which has been shown below.

SELECT * FROM json_table(@restaurant,'$[*]'
COLUMNS(
       id FOR ORDINALITY,
       emp_name VARCHAR(30) path '$.name',
       emp_position VARCHAR(30) path '$.job')
) AS Restaurant_Employee;

From the above output, we have added a new column id that contains the unique identity of each row.

3. EXISTS PATH Columns

EXISTS PATH Columns: It is used to know whether the given path for the values exists or not within the JSON document. It shows the value as 1 for the node value that exists within the JSON document, otherwise as 0 when is node value is not found. The syntax is given below.

name datatype EXISTS PATH path_string

Where,

• name: It is the name of the column.
• datatype: The data type of the column.
• EXISTS PATH: It is used to check whether referred node by path_str within JSON document exists or not.
• path_string: It is the path of the node that we want to check.

Here we are going to use the same JSON document that we created above, which has been shown below.

SELECT * FROM json_table(@restaurant,'$[*]'
COLUMNS(
       id FOR ORDINALITY,
       emp_name VARCHAR(30) path '$.name',
       emp_position VARCHAR(30) path '$.job',
		 emp_salary integer exists path '$.salary')
) AS Restaurant_Employee;

From the output, we can see that the specified path $.salary for the node, The value doesn’t exist within the JSON document so it shows the column value as 0.

4. NESTED PATH Columns

NESTED PATH Column: It is used to produce the rows from the JSON within another JSON or nested JSON structure. The syntax is given below.

NESTED PATH path COLUMNS (column_list)

Where,

• NESTED PATH: It is the keyword to specify that the extract the value from the nested JSON.
• path: It is the path of the nested node with JSON document.
• COLUMNS: It is used to declare the columns just as nested node within the JSON document that is used while generating the relation form.

Here, we are going to create a table Restaurant_Employee with a column emp_name, position and emp_time of data type varchar from the nested JSON document which has been shown below.

Create a new JSON document using the below code.

SET @restaurant = '[
  {"name":"Jhon", "job":"Kitchen_Staff","Time":["9:30 AM","6:00 PM"]},
  {"name":"Paul",  "job":"Manager","Time":["9:30 AM","6:00 PM"]}
]';

Create a table from the above-nested JSON document.

SELECT * FROM json_table(@restaurant,'$[*]'
COLUMNS(
       id FOR ORDINALITY,
       emp_name VARCHAR(30) path '$.name',
       emp_position VARCHAR(30) path '$.job',
		 nested path '$.Time[*]' COLUMNS(
		 emp_timing VARCHAR(10) path '$'
				  )
		 )
) AS Restaurant_Employee;

From the above code and output, we have learned the way to access the nested JSON data with a document and how it is represented as a table.

Here, we have learned about MariaDB JSON_TABLE Function, its syntax with an example.

Read: How to Remove User in MariaDB

MariaDB JSON_TYPE Function

Here, we will learn about MariaDB JSON_TYPE Function, its syntax with an example.

The MariaDB JSON_TYPE function tells the type of data within JSON documents like integer, boolean, array and object.

The syntax of MariaDB JSON_TYPE is given below.

JSON_VALID(value)

Here:

• value – It can be any valid JSON string value.

An example demonstrating the working of the MariaDB JSON_TYPE function on a table containing JSON data is given below.

Here, we are going to create a table Restaurant_Emp with a column id, emp_name, active and timing of data type JSON that contains JSON data which has been shown below.

CREATE TABLE Restaurant_Emp(id JSON , emp_name JSON, active JSON, timing JSON );

Insert the following records in that table.

INSERT INTO Restaurant_Emp(id,emp_name,active,timing)VALUE
('1','{"First_name":"Jhon","Last_name":"Wick"}','true','[9,"to",5]'),
('2','{"First_name":"Paul","Last_name":"Trani"}','true','[5,"to",12]'),
('3','{"First_name":"Jesica","Last_name":"Jones"}','true','[9,"to",5]');

View the created table using the below query.

SELECT * FROM Restaurant_Emp;

Here, we are going to find the type of JSON data within each column of the table i.e. Restaurant_Emp in this example which has been shown below.

SELECT JSON_TYPE(id),JSON_TYPE(emp_name),JSON_TYPE(active),JSON_TYPE(timing) FROM Restaurant_Emp;

From the output, we can see that type of each column contains JSON data.

Here, we have learned about MariaDB JSON_TYPE Function, its syntax with an example.

MariaDB JSON_UNQUOTE Function

Here, we will learn about MariaDB JSON_UNQUOTE Function, its syntax with an example.

The MariaDB JSON_UNQUOTE removes or unquote the escape sequence character from the JSON value. Below is the given escape sequence character.

Escape Sequence Character	Definition of that character
(\”)	It is a double quote
(\b)	It is backslash
(\f)	It is formfeed
(\n)	It is newline
(\r)	It is a carriage return
(\t)	It is a tab
(\\)	It is the backslash (\)
(\uXXXX)	It is the Unicode value of UTF-8 bytes

The syntax of MariaDB JSON_UNQUOTE is given below.

JSON_UNQUOTE(json_val)

Here:

• json_value – It is JSON value that contain the escape sequenc character and we want to remove that.

An example demonstrating the working of the MariaDB JSON_UNQUOTE function on a table that contains the JSON data is given below.

Here, we are going to create a table i.e. Stock_Price and use the JSON_UNQUOTE function on the column to remove the escape sequence character from the value of that column in this example which has been shown below.

CREATE TABLE Stock_Price(stock_name VARCHAR(50),stock_price FLOAT);

Insert the following records.

INSERT INTO Stock_Price(stock_name,stock_price)VALUES
('"Tesla .Inc"',795.35),
('"Apple" .Inc',154.73),
('NVIDIA cortporation',221);

View the table data.

SELECT * FROM Stock_Price;

In the above-created table, we can see that the value of the column stock_name contains the escape sequence character like a double quote.

Use the below code to remove that escape sequence character from column values.

SELECT JSON_UNQUOTE(stock_name) FROM Stock_Price;

Now after running the JSON_UNQUOTE function on the column stock_price removed the double quote around the string in that column.

Here, we have learned about MariaDB JSON_UNQUOTE Function, its syntax with an example.

Read: How to Create View in MariaDB

MariaDB JSON_VALID Function

Here, we will learn about MariaDB JSON_VALID Function, its syntax with an example.

The MariaDB JSON_VALID function returns the value as 1 which means True and 0 which means False for the JSON document that contains valid JSON data. It is used to check whether the given JSON document is valid or not.

The syntax of MariaDB JSON_VALID is given below.

JSON_VALID(value)

Here:

• value- It can be any valid JSON data or column containing JSON data.

An example demonstrating the working of the MariaDB JSON_VALID function on a table containing JSON data is given below.

Here, we are going to use the same table i.e. Restaurant_Emp to check whether a column in this table contains valid JSON data or not, which has been shown below.

SELECT JSON_VALID(id),JSON_VALID(emp_name),JSON_VALID(active),JSON_VALID(timing) FROM Restaurant_Emp;

From the output, we can see that each column and rows show the value as 1 which means all the columns and rows contain the valid JSON data.

Here, we have learned about MariaDB JSON_VALID Function, its syntax with an example.

Read: Replace Function in MariaDB

MariaDB JSON_VALUE Function

Here, we will learn about MariaDB JSON_VALUE Function, its syntax with an example.

The MariaDB JSON_VALUE is used to get the value of keys within JSON documents based on a given path.

The syntax of MariaDB JSON_VALUE is given below.

JSON_VALUE(json_doc, path)

Here:

• json_doc – It is JSON document containing JSON data.
• path – It is used to specify the path of keys whose value we want.

An example demonstrating the working of the MariaDB JSON_VALID function on a table containing JSON data is given below.

Here, we are going to use the same table i.e. Restaurant_Emp to get the value of keys within the JSON data that exist in the column of the table, which has been shown below.

SELECT JSON_VALUE(emp_name,'$.First_name') FROM Restaurant_Emp
WHERE id = '2';

Here, we are finding the first name of the employee whose id is 2.

From the output, we can see that the first name of the employee is Paul whose id is 2.

Here, we have learned about the MariaDB JSON_VALID function, its syntax with an example.

Also, take a look at some more MariaDB tutorials.

• MariaDB Queries – Detailed Guide
• MariaDB ENUM – Helpful Guide

In this tutorial, we have learned about the basics of “MariaDB JSON Functions“, different JSON Functions in MariaDB. Additionally, we have covered the following topics.

• MariaDB JSON Function
• MariaDB Various JSON Function
• MariaDB JSON_ARRAY Function
• MariaDB JSON_ARRAYAGG Function
• MariaDB JSON_ARRAY_APPEND Function
• MariaDB JSON_ARRAY_INSERT Function
• MariaDB JSON_COMPACT Function
• MariaDB JSON_CONTAINS Function
• MariaDB JSON_CONTAINS_PATH Function
• MariaDB JSON_DEPTH Function
• MariaDB JSON_DETAILED Function
• MariaDB JSON_EXIST Function
• MariaDB JSON_EXTRACT Function
• MariaDB JSON_INSERT Function
• MariaDB JSON_KEYS Function
• MariaDB JSON_LENGTH Function
• MariaDB JSON_LOOSE Function
• MariaDB JSON_CONTAINS Function
• MariaDB JSON_MERGE Function
• MariaDB JSON_MERGE_PATCH Function
• MariaDB JSON_MERGE_PRESERVE Function
• MariaDB JSON_OBJECT Function
• MariaDB JSON_OBJECTAGG Function
• MariaDB JSON_QUERY Function
• MariaDB JSON_QUOTE Function
• MariaDB JSON_REMOVE Function
• MariaDB JSON_REPLACE Function
• MariaDB JSON_SEARCH Function
• MariaDB JSON_SET Function
• MariaDB JSON_TABLE Function
• MariaDB JSON_TYPE Function
• MariaDB JSON_UNQUOTE Function
• MariaDB JSON_VALID Function
• MariaDB JSON_VALUE Function