In case you haven't heard, we're now Dolt 1.0!. However, we're not finished. In this blog, we'll go over some recent improvements to Dolt's JSON handling. Additionally, I'll go through some examples of JSON handling to showcase the new functionality.

JSONPath

Dealing with JSON objects can get unwieldy, especially as your application accumulates data over time. As a result, JSONPath is a query language built for selecting and extracting data from JSON documents. Many databases like MySQL have functions that can parse and execute JSONPath queries.

In an effort to be a drop-in replacement for MySQL, we have our very own fork of a popular open source golang jsonpath implementation.

JSON_EXTRACT Improvements

Not too long ago, a customer discovered a functionality gap between Dolt's JSON_EXTRACT implementation, and MySQL's. The asterisk/wildcard operator (*), which deterministically enumerates the values of a JSON Object, returning the results in an array. Naturally, we jumped on the issue and patched our jsonpath implementation.

Now, you can do this in Dolt:

dolt> SELECT JSON_EXTRACT('{"First": "John", "Last": "Smith", "Age": 24}', '$.*');
+----------------------------------------------------------------------+
| JSON_EXTRACT('{"First": "John", "Last": "Smith", "Age": 24}', '$.*') |
+----------------------------------------------------------------------+
| [24, "Smith", "John"]                                                |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Fun fact, MySQL sorts JSON Object keys by length then alphabetically.

Not too long after that, we added support for the -> operator, and the ->> operator. The -> operator is just an alias for JSON_EXTRACT, so column->path is the same as JSON_EXTRACT(column, path). Similarly, the ->> operator is an alias for JSON_UNQUOTE + JSON_EXTRACT, so column->path is the same as JSON_UNQUOTE(JSON_EXTRACT(column, path)).

So this works in Dolt now:

dolt> SELECT * FROM t;
+-----------------------------------------------+
| j                                             |
+-----------------------------------------------+
| {"Age": 24, "Last": "Smith", "First": "John"} |
+-----------------------------------------------+
1 row in set (0.00 sec)

dolt> SELECT j->'$.First' FROM t;
+--------------+
| j->'$.First' |
+--------------+
| "John"       |
+--------------+
1 row in set (0.00 sec)

dolt> SELECT j->>'$.First' FROM t;
+---------------+
| j->>'$.First' |
+---------------+
| John          |
+---------------+
1 row in set (0.00 sec)

JSON_TABLE Improvements

Over the past few days, we've been working to expand our JSON_TABLE functionality. We're adding support for JSON_TABLE column options: FOR ORDINALITY, NULL ON EMPTY/ERROR, DEFAULT <value> ON EMPTY/ERROR, and NESTED. You can read more about what these options do here in the MySQL docs.

The JSON_TABLE function is pretty powerful, allowing users to output well-defined tables from their JSON. Here's an example showcasing some of its capabilities.

dolt> SELECT * FROM
JSON_TABLE(
    '[
        {
            "First": "John",
            "Last": "Smith",
            "Age": 24
        },
        {
            "Last": "Doe",
            "First": "Jane",
            "Age": 21
        },
        {
            "Age": 54,
            "First": "MONEY",
            "Last": {}
        },
        {
            "Last": "I AM"
        }
    ]',
    '$[*]' COLUMNS (
        id FOR ORDINALITY,
        last_name VARCHAR(100) PATH '$.Last' DEFAULT '"BAD"' ON ERROR,
        first_name VARCHAR(100) PATH '$.First' DEFAULT '"MISSING"' ON EMPTY,
        age INT PATH '$.Age' DEFAULT '-1' ON EMPTY
    )
) as employees;

+----+-----------+------------+-----+
| id | last_name | first_name | age |
+----+-----------+------------+-----+
|  1 | Smith     | John       |  24 |
|  2 | Doe       | Jane       |  21 |
|  3 | BAD       | MONEY      |  54 |
|  4 | I AM      | MISSING    |  -1 |
+----+-----------+------------+-----+
4 rows in set (0.0006 sec)

Conclusion

We're constantly improving Dolt, and hopefully we've made life a little easier for those of you that with a ton of loosely structured data in JSON format. There's still work to be done in this particular section of database functionality, so if there's a specific feature you want, make an issue. Feel free to reach out to any of our team members on Discord.