MariaDB includes two selectors that enable us to select elements from JSON arrays:
[N] selects element number N in the array (for example, [0] to select the first element). [*] selects all elements in the array.These can be used in a number of JSON functions that are included in MariaDB. The following examples use them with the JSON_EXTRACT() function in order to return selected array elements.
When working with JSON in MariaDB, you can use JSONPath expressions to manipulate data within a JSON document.
One powerful feature that MariaDB provides is the wildcard step (**). This allows you to recursively select all child elements of the current element.
The wildcard step is a non-standard extension, and it’s also supported with the same meaning in MySQL.
When using SQLcl with Oracle Database, you can export your query results to a JSON file with the SPOOL command.
In MySQL, the JSON_VALUE() function extracts a value from a JSON document at the specified path.
The function was introduced in MySQL 8.0.21.
In Oracle Database, the JSON_OBJECTAGG() function creates a JSON object from a key-value pair.
Typically, the property key, the property value, or both are columns of SQL expressions.
In Oracle Database, the JSON_OBJECT() function creates a JSON object from a sequence of key-value pairs or one object type instance.
In Oracle Database, the JSON_ARRAYAGG() function creates a JSON array from a column of SQL expressions.
In Oracle Database, the JSON_ARRAY() function creates a JSON array from a sequence of SQL scalar expressions or one collection type instance, VARRAY or NESTED TABLE.
In Oracle Database, the JSON_TABLE() function creates a relational view of JSON data. It allows you to present the values in a JSON document in table format – as rows and columns.
In Oracle Database, the JSON_VALUE() function finds a specified scalar JSON value in JSON data and returns it as a SQL value.Â