DuckDB Bag of Tricks: Reading JSON, Data Type Detection, and Query Performance

DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB.

This post is about a particular challenge posted on the DuckDB Discord server which happened to interest me. So, here goes!!

Yesterday on the DuckDB discord server user @col_montium asked:

I've got a bit of a head scratcher. My json from https://api.census.gov/data/2000/dec/sf1/variables.json is a single struct with dynamic keys. I want to extract a table with columns field_code, label, concept, and group. I completed this with a small sample of the data but with the entire dataset the query uses up 24 gigabytes of RAM and then crashes. Here's my query:

WITH source AS (
  SELECT *
  FROM 'https://api.census.gov/data/2000/dec/sf1/variables.json'
),
all_keys AS (
  SELECT unnest( json_keys( variables ) ) AS "key",
         variables
  FROM   source
),
extracted_fields AS (
  SELECT "key" AS field_code
  ,      variables->key->>'$.label' AS label
  ,      variables->key->>'$.concept' AS concept
  ,      variables->key->>'$.predicateType' AS predicate_type
  ,      variables->key->>'$.group' AS "group"
  FROM all_keys
  WHERE "key" NOT IN ('for', 'in', 'ucgid')
)
SELECT *
FROM extracted_fields
ORDER BY field_code

I tried to run this myself on my laptop using the DuckDB 1.1.3 (GA) command line interface, and it failed with a similar error as reported by @col_montium:

Run Time (s): real 252.335 user 197.031250 sys 9.500000
Out of Memory Error: failed to allocate data of size 16.0 MiB (24.9 GiB/25.0 GiB used)

I was able to run it successfully using a 1.1.4 nightly build, but it took about 3 minutes to complete:

Run Time (s): real 188.681 user 148.500000 sys 8.078125

Dataset: Size and Structure

Surprisingly, the source data set is less than 2MB (2,039,748 bytes)! The query also doesn't appear to be terribly complicated, and if it finishes successfully, it yields only 8141 rows. So, clearly something interesting must be the matter with the structure of the dataset.

As it is so modest in size, we can easily inspect it with a text editor:

{
  "variables": {
    "for": {
      "label": "Census API FIPS 'for' clause",
      "concept": "Census API Geography Specification",
      "predicateType": "fips-for",
      "group": "N/A",
      "limit": 0,
      "predicateOnly": true
    },
    "in" {
      "label": "Census API FIPS 'in' clause",
      "concept": "Census API Geography Specification",
      "predicateType": "fips-in",
      "group": "N/A",
      "limit": 0,
      "predicateOnly": true
    },
    "ucgid" {
      "label": "Uniform Census Geography Identifier clause",
      "concept": "Census API Geography Specification",
      "predicateType": "ucgid",
      "group": "N/A",
      "limit": 0,
      "predicateOnly": true,
      "hasGeoCollectionSupport": true
    },
    "P029009" {
      "label": "Total!!In households!!Related child!!Own child!!6 to 11 years",
      "concept": "RELATIONSHIP BY AGE FOR THE POPULATION UNDER 18 YEARS [46]",
      "predicateType": "int",
      "group": "P029",
      "limit": 0
    },
    
    ...many more variables...
    
    "PCT012H185" {
      "label": "Total!!Female!!78 years",
      "concept": "SEX BY AGE (HISPANIC OR LATINO) [209]",
      "predicateType": "int",
      "group": "PCT012H",
      "limit": 0
    }
  }
}    

The structure of the dataset is quite simple: a single object with a single variables property, which has an object value with many object-typed properties. The object-type values of these properties contain a handful of recurring scalar properties like label, concept, predicateType, group and limit.

Now that we analyzed the structure, it should be clear what the query attempts to achieve. It wants to make a row for each property of the outermost variables-object. The property name becomes the field_code column, and forms its natural primary key. The recurring properties of the innermost objects become its non-key columns label, concept, predicate_type and group.

Using read_text() and a DuckDB table column with the JSON-datatype

In an attempt to get the query working without running out of memory, I decided to store the dataset in a DuckDB table like so:

CREATE TABLE t_json
AS
SELECT  filename
,       content::JSON AS data
FROM    read_text('https://api.census.gov/data/2000/dec/sf1/variables.json');

With this in place, a modified version of the initial query might be:

WITH variables AS (
  SELECT  data->variables                         AS variables
  ,       unnest( json_keys( variables ) )        AS field_code
  FROM    t_json
)
SELECT    field_code
,         variables->field_code->>'label'         AS label
,         variables->field_code->>'concept'       AS concept
,         variables->field_code->>'predicateType' AS predicate_type
,         variables->field_code->>'group'         AS group
FROM      variables
WHERE     field_code NOT IN ('for', 'in', 'ucgid')
ORDER BY  field_code

The results look like this:

┌────────────┬──────────────────────────────────┬─────────┬────────────────┬─────────┐
│ field_code │              label               │ concept │ predicate_type │  group  │
│  varchar   │             varchar              │ varchar │    varchar     │ varchar │
├────────────┼──────────────────────────────────┼─────────┼────────────────┼─────────┤
│ AIANHH     │ American Indian Area/Alaska Na…  │ NULL    │ NULL           │ N/A     │
│ AIHHTLI    │ American Indian Area (Off-Rese…  │ NULL    │ NULL           │ N/A     │
│ AITSCE     │ American Indian Tribal Subdivi…  │ NULL    │ NULL           │ N/A     │
│ ANRC       │ Alaska Native Regional Corpora…  │ NULL    │ NULL           │ N/A     │
│ BLKGRP     │ Census Block Group               │ NULL    │ NULL           │ N/A     │
│ BLOCK      │ Census Block                     │ NULL    │ NULL           │ N/A     │
│ CD106      │ Congressional District (106th)   │ NULL    │ string         │ N/A     │
│ CONCIT     │ Consolidated City                │ NULL    │ NULL           │ N/A     │
│   ·        │         ·                        │  ·      │  ·             │  ·      │
│   ·        │         ·                        │  ·      │  ·             │  ·      │
│   ·        │         ·                        │  ·      │  ·             │  ·      │
│ SUBMCD     │ Sub-Minor Civil Division (FIPS)  │ NULL    │ NULL           │ N/A     │
│ SUMLEVEL   │ Summary Level code               │ NULL    │ string         │ N/A     │
│ TRACT      │ Census Tract                     │ NULL    │ NULL           │ N/A     │
│ UA         │ Urban Area                       │ NULL    │ NULL           │ N/A     │
│ US         │ United States                    │ NULL    │ NULL           │ N/A     │
│ ZCTA3      │ ZIP Code Tabulation Area (Thre…  │ NULL    │ NULL           │ N/A     │
│ ZCTA5      │ Zip Code Tabulation Area (Five…  │ NULL    │ NULL           │ N/A     │
├────────────┴──────────────────────────────────┴─────────┴────────────────┴─────────┤
│ 8141 rows (15 shown)                                                     5 columns │
└────────────────────────────────────────────────────────────────────────────────────┘
Run Time (s): real 11.682 user 8.015625 sys 2.406250

Less than 12 seconds. Not super-fast, but still: a pretty substantial improvement as compared to the initial query. What could explain this difference? Two things stand out as compared to the initial query:

• Using the read_text()-function to ingest the data. In the original query, the data was ingested by the first common table expression called source, which did a SELECT directly FROM the dataset's URL 'https://api.census.gov/data/2000/dec/sf1/variables.json'. Clearly, with this syntax, DuckDB will do some magic to perform the appropriate action, which in this case will be to invoke read_json_auto('https://api.census.gov/data/2000/dec/sf1/variables.json').
• Explicitly casting the content of the data file to DuckDB's JSON-datatype. I expected the issue with the original query had something todo with the transformation of JSON data to a tabular result. Having the data in a JSON-datatype seemed like a good start to play with different methods to extract data. The read_text()-function returns the contents of the file as VARCHAR. It cannot and does not attempt to parse or process the data. That's why we need to cast its output explictly to the JSON-type ourselves.

You might object to a comparison as this query does not include the time to fetch the dataset from the internet, nor the time to store it in the DuckDB table. For me that takes about 6 - 10 seconds. However, we can easily rewrite the query to include read_text() directly:

WITH variables AS (
  SELECT  json( content )->variables              AS variables
  ,       unnest( json_keys( variables ) )        AS field_code
  FROM    read_text( 'https://api.census.gov/data/2000/dec/sf1/variables.json' )
)
SELECT    field_code
,         variables->field_code->>'label'         AS label
,         variables->field_code->>'concept'       AS concept
,         variables->field_code->>'predicateType' AS predicate_type
,         variables->field_code->>'group'         AS group
FROM      varibles
WHERE     field_code NOT IN ('for', 'in', 'ucgid')
ORDER BY  field_code

This query takes about 20 seconds, in other words: about the same time as it takes to run the previous query plus the time to fetch the dataset and load it in the table. For a serious comparison of query performance we should probably eliminate the download and use only local files, but we'll get to that later. For now it's enough to notice that this setup is a significant improvement, both in terms of query execution time as well as in stability/memory consumption.

You might also have noticed this alternative query uses slightly different syntax to the extract label, concept, predicateType and group. Also, the final data presentation query is combined with the field extraction logic. However, these changes are really a matter of style. They do not have any bearing on query performance - not for this data set anyway.

Understanding the difference: the JSON-reader

With these results in mind, we have to conclude that the actual operations on the JSON data cannot really explain the difference. So, it must have something todo with the JSON-reader that was implicitly invoked by the initial query. Let's zoom in a bit on the difference between data extracted from a JSON-typed value and data extracted by the JSON-reader.

We know that in the second example, the data is of the JSON-type, because we explicitly cast it to that type. However, that only tells us that the data is essentially text, conforming to the JSON-syntax. It is of course very useful to know the encoding scheme used to represent values and structures, that still does not reveal anything about the content and structure of the data itself.

What data type does the JSON reader think the data has? Let's find out using this DESCRIBE-statement:

DESCRIBE
SELECT *
FROM 'https://api.census.gov/data/2000/dec/sf1/variables.json'

Its result is:

┌─────────────┬─────────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │         column_type         │  null   │   key   │ default │  extra  │
│   varchar   │           varchar           │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ variables   │ STRUCT("for" STRUCT("labe…  │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────────────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 9.541 user 1.968750 sys 0.031250

As you can see, the variables property of the main object that makes up our dataset was detected and projected as a column. DuckDB has also inferred a pretty detailed data type for the column, expressed using DuckDB native SQL data types.

In the example above it's truncated, but if we run it again using .mode line, we can see the entire type descriptor. It goes like this:

STRUCT(
  "for" STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  , predicateOnly BOOLEAN
  )
, "in" STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  , predicateOnly BOOLEAN
  )
, ucgid STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  , predicateOnly BOOLEAN
  , hasGeoCollectionSupport BOOLEAN
  )
, P029009 STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  )
  
  ...many more ...
  
, PCT012H185 STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  )
)

Looks familiar? It should, as it corresponds closely to the structure we witnessed when analyzing the raw JSON data using a text editor.

So, this really is quite different as compared to the JSON-type column, as the JSON-reader must have done some work to explore the structure of the data. Not only did it detect the data type, it also uses the type to represent the data, which in turn determines what operators and functions will be available by the remainder of the query to work with the data.

Whether this is related to the difference in performance and to the out of memory errors, is yet to be determined, but it does not seem unlikely. For starters, the data type closely resembles the data. The textual description of the data type is about half the size of the data itself, which does not seem like a good thing!

We will investigate the JSON-readers' capabilities for data type detection and the possibilities to control that in the next couple of sections. But before we examine that in more detail, let's take a step back and consider what we just learned.

One lesson learned

So far, we've learned at least one important lesson: the JSON reader does in fact do exactly what its name implies - read JSON-encoded data. But while doing so, it tends to output data using DuckDB native data types, and typically not the JSON data type.

In hindsight this certainly sounds perfectly sensible. Yet, both me and @col_montium apparently did not realize that fully, as the original query takes the data coming out of the JSON reader, and processes it using functions like json_keys(), and the extraction operators -> and ->>. These are designed for working with values in the JSON data type, and not for the STRUCT values that the JSON reader hands back to us.

You may wonder: how it is possible that these functions and operators work at all on the STRUCT value returned by the JSON reader? The answer is quite simple: when we apply these functions and operators, they first implicitly cast their arguments and operands to the JSON-type. So, just as the JSON reader spent all its effort to read raw JSON-encoded data and convert it into neat native DuckDB STRUCTs, we immediately undid that effort only to convert them back to a JSON-type. And initially we - or at least I - didn't even realize it.

Actually - it is a little bit different still, as the JSON-reader deals with reading JSON-encoded text whereas the JSON-data type is still a database type. But the point here is that the JSON-reader did work to provide a precise description of the data as STRUCTs, and we didn't bother treating the data as such. Instead, we implicitly cast it to the JSON-type, which is much looser. So it feels there might be an unused opportunity to benefit from the detected data type. We will examine this possibility in the next section.

Avoiding the implicit JSON-type cast?

Now that we have a better understanding of what we've done, could we rewrite the original query so we can avoid the implicit JSON-type cast, and benefit from the DuckDB native types returned by the JSON reader? Somewhat surprisingly, this is not quite as simple as one might think it would (or should) be.

First of all, we cannot simply use whatever are the STRUCT equivalents for the JSON-type functions and operators.

For example, it is currently not possible to extract the keys from a STRUCT type. So there's no straightforward equivalent to how the original query uses json_keys() and then unnest() to spread out the keys from the original variables object into separate rows. (In the DuckDB github repository, @pkoppstein opened a discussion about creating a feature to extract the keys from STRUCTs in case you want to chime in.)

Even if we somehow managed to extract the keys from the STRUCT, there is currently no way to use it to extract the corresponding value. That is, STRUCTs of course support extraction methods, but all of them require the key to be a constant value. This prohibits using a column value, such as what we would get if we'd use an unnested set of keys:

WITH field_codes AS (
  SELECT  unnest( json_keys( variables ) ) AS field_code
  ,       variables
  FROM    'https://api.census.gov/data/2000/dec/sf1/variables.json'
)
SELECT  field_code
,       variables[field_code] AS "variable"
FROM    field_codes
WHERE   field_codes NOT IN ('for', 'in', 'ucgid')

In the example above, variables is the STRUCT-typed value we receive from the JSON-reader. The expression variables[field_code] attempts to extract a value from it using the column value field_code. The statement fails because field_code is not a constant:

Run Time (s): real 8.345 user 1.531250 sys 0.031250
Binder Error: Key name for struct_extract needs to be a constant string

As the error message suggests, the square bracket extraction syntax is just syntactic sugar for the struct_extract() function. Using a function call instead yields the exact same error.

A Solution based on STRUCTs

Eventually I came up with an approach that at least allows me to envision what the solution based on STRUCTs, and without relying on the JSON-type might look like. Here's that attempt:

WITH variables AS (
  UNPIVOT (
    WITH variables AS (
      SELECT  COLUMNS( variables.* EXCLUDE( "for", "in", "ucgid" ) )
      FROM    read_json( 'https://api.census.gov/data/2000/dec/sf1/variables.json' )
    )
    SELECT  struct_pack(
              label := struct_extract( 
                COLUMNS( * )
              , 'label' 
              )
            , concept := struct_extract( 
                COLUMNS( * )
              , 'concept' 
              )
            , predicateType := struct_extract( 
                COLUMNS( * )
              , 'predicateType' 
              )
            , "group" := struct_extract( 
                COLUMNS( * )
              , 'group' 
              )
            )
    FROM    variables
  )
  ON( * )
  INTO NAME field_code VALUE "variable"
)
SELECT field_code, "variable".*
FROM variables
ORDER BY field_code

The main idea here is to use variables.* to turn the keys of the STRUCT given to us by the JSON-reader into separate columns. The UNPIVOT-statement can then turn those columns into rows. In the original query this was achieved by combining json_keys() and unnest().

Since we don't need the metadata-like objects ucgid, for and in, we also don't need to unpack them from the variables object. So, we wrapped variables.* into a COLUMNS-"star expression" so we could add an EXCLUDE-clause to remove them. In the original query, those where removed in the WHERE-clause.

The UNPIVOT-statement not only creates rows from columns, but also lets us transform the column names into values. We use the NAME-clause to collect them into a new field_code column. The VALUE-clause can be used to name the column that receives the corresponding column values. In the example above, that value column is assigned the name variable.

Now, the values extracted from the variables-object are themselves STRUCTs, and they do not all have the same set of keys. From the DuckDB persective, they therefore have distinct types, and thus cannot all simply be lumped together directly into a single column.

However, we're only interested in a particular set of properties. They are 'label', 'concept', 'predicateType', and 'group'. If the type of their value is the same across all objects, then we can extract them and create new STRUCTs having only those keys. This is achieved using struct_extract() and struct_pack().

Again, the COLUMNS(*)-"star expression" proves to be a very useful tool! We use it here as argument for struct_extract(). This way, we need to write the STRUCT extraction-and-assembly business only once. The star expression then applies it to all columns without having to name them explicitly. The STRUCT-value assembled by struct_pack() gets the name of the original column.

The final touch is to unwrap the STRUCTs we assembled into separate columns. This is done in the terminal SELECT, again using variable.* syntax.

Nice! Pity it works only in theory

When we run it, we get:

Run Time (s): real 14.183 user 12.484375 sys 0.062500
Binder Error: Could not find key "concept" in struct

Apparently, not only do the objects in our data set sometimes have more keys than we're interested in, some also don't have all the keys we're attempting to extract. There does not seem to be any way to detect whether some arbitrary STRUCT-value has a specific key, so we can't simply work around it.

If we comment out the extraction for the the concept key, we get the same error but now for the predicateType key. If you comment out that extraction too, the query executes succesfully. On my laptop, it takes about as long as the original query, that is to say, much worse than the alternative using the JSON-type.

So even if we would somehow be able to overcome the issues with extracting the properties and creating the STRUCT, we still can't seem to really benefit from the more strict typing. I guess the main take-away here is, we can STRUCTggle all we want, but our data simply appears not to be given to us in a way that allows it to work for us.

JSON reader Parameters

We mentioned earlier that SELECT-ing directly from the url, as the original query did, causes DuckDB to invoke read_json() or read_json_auto(), which are synonyms of each other. If you like, you can convince yourself by running an equivalent DESCRIBE-statement that explicitly invokes the reader:

DESCRIBE
SELECT *
FROM read_json( 'https://api.census.gov/data/2000/dec/sf1/variables.json' )

If you execute it, you'll notice that the inferred column name and data type are identical to that returned by the prior DESCRIBE-statement. But invoking the reader explicitly has a benefit in that it offers us the possibility to pass parameters to control its behavior. Some relevant to the topic at hand are:

BOOLEAN auto_detect

Whether to auto detect the schema at all. Contrary to what the current documentation states, the default value is TRUE rather than FALSE.

STRUCT(name VARCHAR, type VARCHAR) columns

If you choose auto_detect to be FALSE, you are required to explicitly specify the columns and column types.

INTEGER maximum_depth

The number of nesting levels that are considered when detecting the datatype. The default is -1, which means there is no restriction on the depth of type detection. When set to a positive integer, and there are object-type values at the maximum level of nesting, then those will get 'detected' as being of the JSON-type.

Controlling the depth of the JSON-reader's type detection

Now that we learned about the maximum_depth parameter, let's apply it and experience its effect:

DESCRIBE
SELECT  *
FROM    read_json( 
          'https://api.census.gov/data/2000/dec/sf1/variables.json' 
        , maximum_depth = 0
        )

At maximum_depth = 0, there is no detection at all, and the entire data set is just a JSON-typed value:

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ json        │ JSON        │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 7.583 user 0.375000 sys 0.015625

This is on par from what we achieved with the read_text() and explicitly casting its content column to the JSON-type. Let's allow for one level:

DESCRIBE
SELECT  *
FROM    read_json( 
          'https://api.census.gov/data/2000/dec/sf1/variables.json' 
        , maximum_depth = 1
        )

At maximum_depth = 1 we get at least extraction into separate columns. However, the column data type will still be the generic JSON-type:

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ variables   │ JSON        │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 8.521 user 0.343750 sys 0.000000

We could allow another level:

DESCRIBE
SELECT  *
FROM    read_json( 
          'https://api.census.gov/data/2000/dec/sf1/variables.json' 
        , maximum_depth = 2
        )

At maximum_depth = 2, the type detected for the variables column is a bag of properties of the JSON-type. In the DuckDB type system, this is represented as MAP( VARCHAR, JSON ):

┌─────────────┬────────────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │    column_type     │  null   │   key   │ default │  extra  │
│   varchar   │      varchar       │ varchar │ varchar │ varchar │ varchar │
├─────────────┼────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ variables   │ MAP(VARCHAR, JSON) │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴────────────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 8.229 user 0.343750 sys 0.000000

This is interesting! So far we've seen STRUCTs, but not MAPs before.

JSON-objects as DuckDB MAPs

Let's attempt to rewrite the original query using the MAP( VARCHAR, JSON )-typed value we get from the JSON-reader when we pass maximum_depth = 2:

WITH variables AS (
  SELECT  unnest( map_keys( variables ) )   AS field_code
  ,       unnest( map_values( variables ) ) AS "variable"
  FROM    read_json( 
            'https://api.census.gov/data/2000/dec/sf1/variables.json' 
          , maximum_depth = 2
          )
)
SELECT    field_code
,         "variable"->>'label'              AS label
,         "variable"->>'concept'            AS concept
,         "variable"->>'predicateType'      AS predicate_type
,         "variable"->>'group'              AS group
FROM      variables
WHERE     field_code NOT IN ('for', 'in', 'ucgid')
ORDER BY  field_code

The main difference as compared to using only the JSON-type, is that the MAP-type lets us extract both the keys as well as the corresponding values using map_keys() and map_values() respectively. This means the final SELECT does not have to extract the variable object explicitly using the key: instead, we can immediately extract properties of interest from the variable objects.

In addition, the main difference with the original query is that the JSON-reader now hands the variable objects to us as JSON-typed values. The original query relied on implicit typecasting, which does not happen here.

If we download the JSON file and store it on the local disc, and modify our queries to use that, the results are quite remarkable: the one using read_text() and the explicit cast to the JSON-type takes about 12 seconds. However, that result is smashed completely by the last query, which now takes just half a second! A really nice improvement!

Conclusion

• DuckDB's feature to SELECT directly from a file or URL is convenient, but it pays off to examine the underlying reader invocation and its parameters.
• When using the JSON-reader, it's a good idea to examine whether the detected datatype is suitable for the extraction you're attempting to perform. By default, the JSON-reader will exhaustively detect the type. If you find the detected type is large as compared to your dataset, and/or more precise or detailed than required for your purpose, try using the maximum_depth parameter to curtail the type detection.
• If you're using the JSON-reader and find that your extraction logic relies on JSON-type functions and operators, then beware of implicit casting of your extracted data to the JSON-type. Implicit casting to the JSON-type may point to an opportunity to limit the level of data type detection.
• When reading JSON-data, you can try extracting from a JSON-typed value - either by loading your data to a table with a JSON-typed column, or using a combination of read_text() and an explicit cast to the JSON-type. Even if your goal is use the JSON-reader, the JSON-typed value is a useful baseline: if you find that your JSON-reader based query is slower than the one on the JSON-typed value, it means you have an opportunity to improve.
• If you're using json_keys() with the purpose of extracting objects or values from the JSON-data, then consider using the JSON-reader and configuring it so that it returns a MAP-type. The MAP functions map_keys() and map_values() are really fast, and may help you avoid an an extra step to use the key to extract an object.
• It would be nice if STRUCTs would be a little bit more flexible. For example, I'd like to be able to extract field values non-constant expressions. It would also be useful to have a function to obtain their keys, and it would help to check if it has a particular key. Another feature that would also have helped is if it would be possible to define STRUCT-fields to be optional, or to have a default value.

MySQL: a few observations on the JSON type

MySQL 5.7 comes with built-in JSON support, comprising two major features:

• A native JSON data type
• A set of built-in functions to manipulate values of the JSON type

Despite being added rather recently (in MySQL 5.7.8 to be precise - one point release number before the 5.7.9 GA version), I feel the JSON support so far looks rather useful. Improvements are certainly possible, but compared to for example XML support (added in 5.1 and 5.5), the JSON feature set added to 5.7.8 is reasonably complete, coherent and standards-compliant.

(We can of course also phrase this more pessimistically and say that XML support falls short on these accounts, but that's not what this post is about :-)

There is potentially a lot to write and explain about the JSON support, and I can't hope to completely cover the subject in one blog post. Rather, I will highlight a few things I observed and I hope that this will be helpful for others that want to get started with JSON in MySQL 5.7.

Creating JSON values

There are a number of ways to create values of the JSON type:

Using CAST(... AS JSON)

CAST a value of any non-character string type AS JSON to obtain a JSON representation of that value. Example:

mysql> SELECT CAST(1 AS JSON), CAST(1.1 AS JSON), CAST(NOW() AS JSON);
+-----------------+-------------------+------------------------------+
| CAST(1 AS JSON) | CAST(1.1 AS JSON) | CAST(NOW() AS JSON)          |
+-----------------+-------------------+------------------------------+
| 1               | 1.1               | "2015-10-31 23:01:56.000000" |
+-----------------+-------------------+------------------------------+
1 row in set (0.00 sec)

Even though it may not be immediately clear from the result, the CAST operation actually turned these values into JSON equivalents. More about this in the next section.

If the value you're casting is of a character string type, then its value should be parseable as either a JSON object or a JSON array (i.e., JSON documents), as a JSON keyword indicating a built-in value, like null, true, false, or as a properly quoted JSON string value:

mysql> SELECT CAST('{}' AS JSON) object
    -> ,      CAST('[]' AS JSON) array
    -> ,      CAST('null' AS JSON) "null"
    -> ,      CAST('true' AS JSON) "true"
    -> ,      CAST('false' AS JSON) "false"
    -> ,      CAST('"string"' AS JSON) string
    -> ;
+--------+-------+------+------+-------+----------+
| object | array | null | true | false | string   |
+--------+-------+------+------+-------+----------+
| {}     | []    | null | true | false | "string" |
+--------+-------+------+------+-------+----------+
1 row in set (0.00 sec)

If the string is not parseable as JSON, you'll get a runtime error:

mysql> SELECT CAST('' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "The document is empty." at position 0 in ''.
mysql> SELECT CAST('{]' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Missing a name for object member." at position 1 in '{]'.

Note that many keywords that might be valid in other environments, like NaN, Infinity, javascript built-in constructor fields like Number.EPSILON, and even undefined are *not* valid in this context. Remember - this is JSON, not javascript.

To get the JSON presentation of a plain, unquoted string value, you can use the JSON_QUOTE() function:

mysql> SELECT JSON_QUOTE(''), JSON_QUOTE('{]');
+----------------+------------------+
| JSON_QUOTE('') | JSON_QUOTE('{]') |
+----------------+------------------+
| ""             | "{]"             |
+----------------+------------------+
1 row in set (0.00 sec)

SELECT-ing from a JSON column

Table columns can be defined to be of JSON data type, and SELECT-ing from such a column will create JSON values at runtime. Of course, such a column would first need to be populated before it yields JSON values, and this can be done simply with an INSERT statement. When INSERT-ing non-JSON type values into a column of the JSON type, MySQL will behave as if it first converts these values to JSON-type, just as if it would apply CAST(value AS JSON) to those values.

UPDATE: Giuseppe Maxia kindly pointed out that there is an issue when INSERT-ing the result of LOAD_FILE() into a JSON column. You first have to CONVERT() the -binary- result to the utf8 character set before it can be successfully accepted as JSON. This was reported as bug #79066.

Calling functions that return JSON values

The JSON_QUOTE() function mentioned above is one example of built-in functions returning a JSON value. To create new JSON documents from scratch, JSON_OBJECT() and JSON_ARRAY() are probably most useful:

mysql> SELECT JSON_ARRAY(1, 2, 3) array, JSON_OBJECT('name1', 'value1', 'name2', 'value2') object;
+-----------+----------------------------------------+
| array     | object                                 |
+-----------+----------------------------------------+
| [1, 2, 3] | {"name1": "value1", "name2": "value2"} |
+-----------+----------------------------------------+
1 row in set (0.00 sec)

Note that we could have achieved the previous result also by CASTing literal string representations of these JSON documents AS JSON:

mysql> SELECT CAST('[1, 2, 3]' AS JSON) array, CAST('{"name1": "value1", "name2": "value2"}' AS JSON) object;
+-----------+----------------------------------------+
| array     | object                                 |
+-----------+----------------------------------------+
| [1, 2, 3] | {"name1": "value1", "name2": "value2"} |
+-----------+----------------------------------------+
1 row in set (0.00 sec)

However, as we shall see later on, this approach is not entirely equivalent to constructing these documents through JSON_ARRAY and JSON_OBJECT.

There are many more built-in JSON functions that return a value of the JSON data type. Unlike JSON_QUOTE(), JSON_ARRAY() and JSON_OBJECT(), most of these also require a JSON document as their first argument. In these cases, the return value represents a modified instance of the document passed as argument.

Operating on JSON documents: Extraction and Modification

While the JSON document may be a convenient unit for storing and transporting related items of data, any meaningful processing of such documents will always involve some operation to transform or modify such a document: for example, extracting some item stored inside the document, or adding or removing properties or array elements.

Manipulation of JSON documents always involves at least two distinct items:

• The JSON document to operate on. This can be an explicit or implicitly obtained JSON document, constructed in any of the ways described earlier in this post. In general, functions that manipulate JSON documents accept the document that is being operated on as their first argument.
• A path. The path is an expression that identifies which part of the document to operate on. In general, the second argument of functions that manipulate JSON documents is a path expression. Depending on which function exactly, other arguments may or may not accept path expressions as well.

It is important to point out that none of the functions that modify JSON documents actually change the argument document inline: JSON functions are pure functions that don't have side effects. The modified document is always returned from the function as a new document.

JSON path expressions in MySQL

While the path is passed as a string value, it's actually an expression consisting of alternating identifiers and access operators that as a whole identifies a particular piece within the JSON document:

Identifiers

There are 4 types of identifiers that can appear in a path:

• $ (dollar sign) is a special identifier, which is essentially a placeholder for the current document being operated on. It can only appear at the start of the path expression
• Property names are optionally double quoted names that identify properties ("fields") in a JSON object. Double quoted property names are required whenever the property name contains meta characters. For example, if the property name contains any interpunction or space characters, you need to double quote the name. A property name can appear immediately after a dot-access operator.
• Array indices are integers that identify array elements in a JSON array. Array indices can appear only within an array-access operator (which is denoted by a pair of square braces)
• * (asterisk) is also a special identifier. It indicates a wildcard that represents any property name or array index. So, the asterisk can appear after a dot-operator, in which case it denotes any property name, or it may appear between square braces, in which case it represents all existing indices of the array.
  
  The asterisk essentially "forks" the path and may thus match multiple values in a JSON document. The MySQL JSON functions that grab data or meta data usually have a way to handle multiple matched values, but JSON functions that modify the document usually do not support this.

Access operators

Paths can contain only 2 types of access operators:

• dot-operator, denoted by a .-character. The dot-operator can appear in between any partial path expression and an identifier (including the special wildcard identifier *). It has the effect of extracting the value identified by the identifier from the value identified by the path expression that precedes the dot.
  
  This may sound more complicated than it really is: for example, the path $.myproperty has the effect of extracting whatever value is associated with the top-level property called myproperty; the path $.myobject.myproperty has the effect of extracting the value associated with the property called myproperty from the nested object stored in the myobject property of the top-level document.
• array access-operator, denoted by a matching pair of square braces: [...]. The braces should contain either an integer, indicating the position of an array element, or the * (wildcard identifier) indicating all array element indices.
  
  The array-access operator can appear after any path expression, and can be followed by either a dot-operator (followed by its associated property identifier), or another array access operator (to access nested array elements).
  
  Currently, the braces can be used only to extract array elements. In javascript, braces can also contain a quoted property name to extract the value of the named property (equivalent to the dot-operator) but this is currently not supported in MySQL path expressions. (I believe this is a - minor - bug, but it's really no biggie since you can and probably should be using the dot-operator for properties anyway.)

Below is the syntax in a sort of EBNF notation in case you prefer that:

  mysql-json-path         ::= Document-placeholder path-expression?
  Document-placeholder    ::= '$'
  path-expression         ::= path-component path-expression*
  path-component          ::= property-accessor | array-accessor
  property-accessor       ::= '.' property-identifier
  property-identifier     ::= Simple-property-name | quoted-property-name | wildcard-identifier
  Simple-property-name    ::= <Please refer to JavaScript, The Definitive Guide, 2.7. Identifiers>
  quoted-property-name    ::= '"' string-content* '"'
  string-content          ::= Non-quote-character | Escaped-quote-character
  Non-quote-character     ::= <Any character except " (double quote)>
  Escaped-quote-character ::= '\"'
  wildcard-identifier     ::= '*'
  array-accessor          ::= '[' element-identifier ']'
  element-identifier      ::= [0-9]+ | wildcard-identifier

Grabbing data from JSON documents

json JSON_EXTRACT(json, path+)

This functions gets the value at the specified path. Multiple path arguments may be passed, in which case any values matching the paths are returned as a JSON array.

json json-column->path

If you have a table with a column of the JSON type, then you can use the -> operator inside SQL statements as a shorthand for JSON_EXTRACT(). Note that this operator only works inside SQL statements, and only if the left-hand operand is a column name; it does not work for arbitrary expressions of the JSON type. (Pity! I would love this to work for any expression of the JSON type, and in any context - not just SQL statements)

Grabbing metadata from JSON documents

bool JSON_CONTAINS(json, value, path?)

Checks whether the specified value appears in the specified document. If the path is specified, the function returns TRUE only if the value appears at the specified path. If the path argument is omitted, the function looks *anywhere* in the document and returns TRUE if it finds the value (either as property value or as array element).

bool JSON_CONTAINS_PATH(json, 'one'|'all', path+)

Checks whether the specified JSON document contains one or all of the specified paths. Personally I think there are some issues with this function

int JSON_DEPTH(json)

Number of levels present in the document

json-array JSON_KEYS(json-object, path?)

Returns the property names of the specified object as a JSON-array. If path is specified, the properties of the object identified by the path are returned instead.

int JSON_LENGTH(json, path?)

Returns the number of keys (when the json document is an object) or the number of elements (in case the json document is an array). If a path is specified, the function is applied to the value identified by the path rather than the document itself. Ommitting the path is equivalent to passing $ as path.

string JSON_SEARCH(json, 'one'|'all', pattern, escape?, path*)

Searches for string values that match the specified pattern, and returns the path or paths where the properties that match the pattern are located. The second argument indicates when the search should stop - in case it's 'one', search will stop as soon as a matching path was found, and the path is returned. In case of 'all', search will continue until all matching properties are found. If this results in multiple paths, then a JSON array of paths will be returned. The pattern can contain % and _ wildcard characters to match any number of characters or a single character (just as with the standard SQL LIKE-operator). The escape argument can optionally define which character should be used to escape literal % and _ characters. By default this is the backslash (\). Finally, you can optionally limit which parts of the document will be searched by passing one or more json paths. Technically it is possible to pass several paths that include the same locations, but only unique paths will be returned. That is, if multiple paths are found, the array of paths that is returned will never contain the same path more than once.

Unfortunately, MySQL currently does not provide any function that allows you to search for property names. I think it would be very useful so I made a feature request.

string JSON_TYPE(json)

Returns the name of the type of the argument value. It's interesting to note that the set of type values returned by this function are not equivalent to the types that are distinguished by the JSON specification. Values returned by this function are all uppercase string values. Some of these indicate items that belong to the JSON type system, like: "OBJECT", "ARRAY", "STRING", "BOOLEAN" and "NULL" (this is the uppercase string - not to be confused with the keyword for the SQL literal NULL-value). But some refer to native MySQL data types: "INTEGER", "DOUBLE", and "DECIMAL"; "DATE", "TIME", and "DATETIME", and "OPAQUE".

bool JSON_VALID(string)

Returns whether the passed value could be parsed as a JSON value. This is not limited to just JSON objects and arrays, but will also parse JSON built-in special value keywords, like null, true, false.

Manipulating JSON documents

json JSON_INSERT(json, [path, value]+)

Takes the argument json document, and adds (but does not overwrite) properties or array elements. Returns the resulting document.

json JSON_MERGE(json, json+)

Folds multiple documents and returns the resulting document.

json JSON_REMOVE(json, path+)

Remove one or more items specified by the path arguments from the document specified by the JSON argument, and returns the document after removing the specified paths.

json JSON_REPLACE(json, [path, value]+)

Takes the argument document and overwrites (but does not add) items specified by path arguments, and returns the resulting document.

json JSON_SET(json, [path, value]+)

Takes the argument document and adds or overwrites items specified by the path arguments, then returns the resulting document.

Functions to manipulate JSON arrays

json JSON_ARRAY_APPEND(json, [path, value]+)

If the path exists and identifies an array, it appends the value to the array. If the path exists but identifies a value that is not an array, it wraps the value into a new array, and appends the value. If the path does not identify a value at all, the document remains unchanged for that path.

json JSON_ARRAY_INSERT(json, [array-element-path, value]+)

This function inserts elements into existing arrays. The path must end with an array accessor - it must end with a pair of square braces containing an exact array index (not a wildcard). If the partial path up to the terminal array accessor identies an existing array, and the specified index is less than the array length, the value is inserted at the specified position. Any array elements at and beyond the specified position are shifted down one position to make room for the new element. If the specified index is equal to or exceeds the array length, the new value is appended to the array.

int JSON_LENGTH(json, path?)

I already described this one as a function that grabs metadata, but I found this function to be useful particularly when applied arrays.

Removing array elements

Note that there is no dedicated function for removing elements from an array. It is simply done using JSON_REMOVE. Just make sure the path argument denotes an array accessor to identify the element to remove.

To remove multiple elements from an array, you can specify multiple path arguments. In this case, the removal operation is performed sequentially, evaluating all passed path arguments from left to right. So, you have to be very careful which path to pass, since a preceding path may have changed the array you're working on. For example, if you want to remove the first two elements of an array, you should pass a path like '$[0]' twice. Passing '$[0]' and '$[1]' will end up removing elements 0 and 2 of the original array, since after removing the initial element at '$[0]', the element that used to sit at position 1 has been shifted left to position 0. The element that then sits at position 1 is the element that used to sit at position 2:

mysql> select json_remove('[1,2,3,4,5]', '$[0]', '$[0]') "remove elements 0 and 1"
    -> ,      json_remove('[1,2,3,4,5]', '$[0]', '$[1]') "remove elements 0 and 2"
    -> ;
+-------------------------+-------------------------+
| remove elements 0 and 1 | remove elements 0 and 2 |
+-------------------------+-------------------------+
| [3, 4, 5]               | [2, 4, 5]               |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

Concatenating arrays

There is no function dedicated to concatenating arrays. However, you can use JSON_MERGE to do so:

mysql> SELECT JSON_MERGE('[0,1]', '[2,3]');
+------------------------------+
| JSON_MERGE('[0,1]', '[2,3]') |
+------------------------------+
| [0, 1, 2, 3]                 |
+------------------------------+
1 row in set (0.00 sec)

Slicing arrays

There is no dedicated function or syntax to take a slice of an array. If you don't need to slice arrays, then good - you're lucky. If you do need it, I'm afraid you're up for a challenge: I don't think there is a convenient way to do it. I filed a feature request and I hope this will be followed up.

JSON Schema Validation

Currently, the JSON functions provide a JSON_VALID() function, but this can only check if a string conforms to the JSON syntax. It does not verify whether the document conforms to predefined structures (a schema).

I anticipate that it might be useful to be able to ascertain schema conformance of JSON documents within MySQL. The exact context is out of scope for this post, but I would already like to let you know that I am working on a JSON schema validator. It can be found on github here: mysql-json-schema-validator.

Stay tuned - I will do a writeup on that as soon as I complete a few more features that I believe are essential.

MySQL JSON is actually a bit like BSON

MySQL's JSON type is not just a blob with a fancy name, and it is not entirely the same as standard JSON. MySQL's JSON type is more like MongoDB's BSON: it preserves native type information. The most straightforward way to make this clear is by creating different sorts of JSON values using CAST( ... AS JSON) and then reporting the type of the result using JSON_TYPE:

mysql> SELECT  JSON_TYPE(CAST('{}' AS JSON)) as "object"
    -> ,       JSON_TYPE(CAST('[]' AS JSON)) as "array"
    -> ,       JSON_TYPE(CAST('""' AS JSON)) as "string"
    -> ,       JSON_TYPE(CAST('true' AS JSON)) as "boolean"
    -> ,       JSON_TYPE(CAST('null' AS JSON)) as "null"
    -> ,       JSON_TYPE(CAST(1 AS JSON)) as "integer"
    -> ,       JSON_TYPE(CAST(1.1 AS JSON)) as "decimal"
    -> ,       JSON_TYPE(CAST(PI() AS JSON)) as "double"
    -> ,       JSON_TYPE(CAST(CURRENT_DATE AS JSON)) as "date"
    -> ,       JSON_TYPE(CAST(CURRENT_TIME AS JSON)) as "time"
    -> ,       JSON_TYPE(CAST(CURRENT_TIMESTAMP AS JSON)) as "datetime"
    -> ,       JSON_TYPE(CAST(CAST('""' AS BINARY) AS JSON)) as "blob"
    -> \G
*************************** 1. row ***************************
  object: OBJECT
   array: ARRAY
  string: STRING
 boolean: BOOLEAN
    null: NULL
 integer: INTEGER
 decimal: DECIMAL
  double: DOUBLE
    date: DATE
    time: TIME
datetime: DATETIME
    blob: BLOB
1 row in set (0.00 sec)

What this query shows is that internally, values of the JSON type preserve native type information. Personally, I think that is a good thing. JSON's standard type system is rather limited. I would love to see standard JSON support for proper decimal and datetime types.

Comparing JSON objects to JSON objects

The MySQL JSON type system is not just cosmetic - the attached internal type information affects how the values work in calculations and comparisons. Consider this comparison of two JSON objects:

mysql> SELECT CAST('{"num": 1.1}' AS JSON) = CAST('{"num": 1.1}' AS JSON);
+-------------------------------------------------------------+
| CAST('{"num": 1.1}' AS JSON) = CAST('{"num": 1.1}' AS JSON) |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

This is already quite nice - you can't compare two objects like that in javascript. Or actually, you can, but the result will be false since you'd be comparing two distinct objects that simply happen to have the same properties and property values. But usually, with JSON, we're just interested in the data. Since the objects that are compared here are totally equivalent with regard to composition and content, I consider the ability to directly compare objects as a bonus.

It gets even nicer:

mysql> SELECT CAST('{"num": 1.1, "date": "2015-11-01"}' AS JSON) = CAST('{"date": "2015-11-01", "num": 1.1}' AS JSON);
+---------------------------------------------------------------------------------------------------------+
| CAST('{"num": 1.1, "date": "2015-11-01"}' AS JSON) = CAST('{"date": "2015-11-01", "num": 1.1}' AS JSON) |
+---------------------------------------------------------------------------------------------------------+
|                                                                                                       1 |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Again, the result is true, indicating that these objects are equivalent. But you'll notice that the property names appear in different order between these two objects. But the direct comparison ignores the property order - it only takes into account whether a property exists at a particular path, and whether the property values are the same. One can argue about whether the property order should be deemed significant in a comparsison. The JSON spec doesn't specify so. But I'm inclined to say that MySQL's behavior here is a nice feature.

Now let's try something a bit like that first comparison, but in a slightly different way:

mysql> SELECT  JSON_OBJECT('bla', current_date)
    -> ,       JSON_OBJECT('bla', current_date) = JSON_OBJECT('bla', current_date)
    -> ,       JSON_OBJECT('bla', current_date) = CAST('{"bla": "2015-11-01"}' AS JSON)
    -> \G
*************************** 1. row ***************************
                                        JSON_OBJECT('bla', current_date): {"bla": "2015-11-01"}
     JSON_OBJECT('bla', current_date) = JSON_OBJECT('bla', current_date): 1
JSON_OBJECT('bla', current_date) = CAST('{"bla": "2015-11-01"}' AS JSON): 0
1 row in set (0.00 sec)

The difference here is of course creating the object using JSON_OBJECT as opposed to using CAST(... AS JSON). While the string representation of the result of JSON_OBJECT('bla', current_date) looks exactly the same like that of CAST('{"bla": "2015-11-01"}' AS JSON), they are not equivalent: in the case of JSON_OBJECT, MySQL internally attached native type information to the property which is of the type DATE (a type that does not exist in standard JSON), whereas in the case of the CAST(... AS JSON) operation, MySQL did not have any additional type information for the value of the property, leaving it no other choice than to assume a STRING type. The following query proves the point:

mysql> SELECT  JSON_TYPE(JSON_EXTRACT(JSON_OBJECT('bla', current_date), '$.bla'))
    -> ,       JSON_TYPE(JSON_EXTRACT(CAST('{"bla": "2015-11-01"}' AS JSON), '$.bla'))
    -> \G
*************************** 1. row ***************************
     JSON_TYPE(JSON_EXTRACT(JSON_OBJECT('bla', current_date), '$.bla')): DATE
JSON_TYPE(JSON_EXTRACT(CAST('{"bla": "2015-11-01"}' AS JSON), '$.bla')): STRING
1 row in set (0.00 sec)

Comparing JSON values to non-JSON values

Fortunately, comparison of JSON values to MySQL non-JSON values is pretty consistent, without requiring explicit CAST operations. This may sound obvious, but it's really not. The following query might explain better what I mean. Consider a JSON object with a property called "myProp" that has a string value of "value1":

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('myProp', 'value1'), '$.myProp');
+-----------------------------------------------------------+
| JSON_EXTRACT(JSON_OBJECT('myProp', 'value1'), '$.myProp') |
+-----------------------------------------------------------+
| "value1"                                                  |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

Note the double quotes around the value - when we extract the value of the myProp property, the result is a JSON string - not a native MySQL character type. And when that result is rendered by the client, its MySQL string representation includes the double quotes. To get a proper MySQL string, we can apply JSON_UNQUOTE(), like this:

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_OBJECT('myProp', 'value1'), '$.myProp'));
+-------------------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(JSON_OBJECT('myProp', 'value1'), '$.myProp')) |
+-------------------------------------------------------------------------+
| value1                                                                  |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

But fortunately, we don't really need to apply JSON_UNQUOTE() for most operations. For example, to compare the extracted value with a regular MySQL string value, we can simply do the comparison without explicitly casting the MySQL string to a JSON type, or explicitly unquoting the JSON string value to a MySQL string value:

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('myProp', 'value1'), '$.myProp') = 'value1';
+----------------------------------------------------------------------+
| JSON_EXTRACT(JSON_OBJECT('myProp', 'value1'), '$.myProp') = 'value1' |
+----------------------------------------------------------------------+
|                                                                    1 |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Again, I think this is very good news!

Still, there definitely are some gotcha's. The following example might explain what I mean:

mysql> SELECT  CURRENT_DATE
    -> ,       CURRENT_DATE = '2015-11-01'
    -> ,       JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp')
    -> ,       JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp') = '2015-11-01'
    -> ,       JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp') = CURRENT_DATE
    -> ,       JSON_UNQUOTE(JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp')) = '2015-11-01'
    -> \G
*************************** 1. row ***************************
                                                                              CURRENT_DATE: 2015-11-01
                                                               CURRENT_DATE = '2015-11-01': 1
                             JSON_EXTRACT(JSON_OBJECT('myProp', current_date), '$.myProp'): "2015-11-01"
              JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp') = '2015-11-01': 0
              JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp') = CURRENT_DATE: 1
JSON_UNQUOTE(JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp')) = '2015-11-01': 1
1 row in set (0.00 sec)

Note that this is the type of thing that one might easily get wrong. The comparison CURRENT_DATE = '2015-11-01' suggests the MySQL date value is equal to its MySQL string representation, and the comparison JSON_EXTRACT(JSON_OBJECT('myProp', current_date), '$.myProp') = CURRENT_DATE suggests the value extracted from the JSON document is also equal to the date value.

From these two results one might expect that JSON_EXTRACT(JSON_OBJECT('myProp', CURRENT_DATE), '$.myProp') would be equal to '2015-11-01' as well, but the query clearly shows this is not the case. Only when we explicitly apply JSON_UNQUOTE does the date value extracted from the JSON document become a real MySQL string, which we then can compare with the string value '2015-11-01' successfully.

When you think about a minute what really happens, it does make sense (at least, I think it does):

• A MySQL date is equivalent to the MySQL string representation of that date
• A MySQL date is equivalent to it's JSON date representation
• A JSON date is not equal to the MySQL string representation of that date
• A MySQL string representation of a JSON date is equal to the MySQL string representation of that date

That said, you might still find it can catch you when off guard.

Table columns of the JSON type

The JSON type is not just a runtime type - it is also available as a storage data type for table columns. A problem though is that there is no direct support for indexing JSON columns, which is sure to become a problem in case you plan to query the table based on the contents of the JSON document. Any WHERE, JOIN...ON, GROUP BY or ORDER BY-clause that relies on extracting a value from the JSON column is sure to result in a full table scan.

There is a workaround though: Once you know the paths for those parts of the document that will be used to filter, order and aggregate the data, you can create generated columns to have these values extracted from the document, and then put an index on those generated columns. This practice is recommended for MySQL by the manual page for CREATE TABLE. A complete example is given in the section called Secondary Indexes and Virtual Generated Columns.

Obviously, this approach is not without issues:

• You need to rewrite your queries accordingly to use those generated columns rather than the raw extraction operations on the document. Or at least, you will have to if you want to benefit from your indexes.
• Having to create separate columns in advance seems at odds with schema flexibility, which I assume is a highly-valued feature for those that find they need JSON columns.
• The generated columns will require additional storage.

Of these concerns, I feel that the need to rewrite the queries is probably the biggest problem.

UPDATE: Roy Lyseng kindly pointed out to me that I missed an important feature. MySQL is actually smart enough to use indexed generated columns on the json document. Just look at this query:

mysql> explain select doc from posts where json_extract(doc, '$.Id') = 1;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | posts | NULL       | const | id            | id   | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+

Note how the query does not directly reference the generated column id. MySQL magically understands that json_extract(doc, '$.Id') was used as expression for a generated column, and this is enough to automatically include it in the plan evaluation. Thanks Roy! This is marvelous :)

The additional storage seems to be the smallest issue, assuming the number of items that you need to index is small as compared to the entire document. (Although I can imagine the extra storage would start to count when you want to extract large text columns for full-text indexing). That said, if I understand correctly, if you create the index on VIRTUAL generated columns, only the index will require extra storage - there won't also be storage required for the columns themselves. (Note that creating an index will always require extra storage - that's just how it works, both in MySQL, as well as in specialized document databases like MongoDB.)

As far as I can see now, any indexing scheme that requires us to elect the items within the documents that we want to index in advance suffers from the same drawback: If the schema evolves in such a way that fields that used to be important enough to be deemed fit for indexing get moved or renamed often, then this practice will affect all aspects of any application that works on the document store. My gut feeling is that despite the theoretical possibility of schema flexibility, this will cause enough inertia in the schema evolution (at least, with respect to those items that we based our indexes on) to be well in time to come up with other solutions. To be fair though, having to set up generated columns would probably add a some extra inertia as compared to a pure document database (like MongoDB).

But my main point still stands: if you choose to keep changing the schema all the time, especially if it involves those items that you need to filter, sort, or aggregate the data, then the changes will affect almost every other layer of your application - not just your database. Apparently, that's what you bargained for and in the light of all other changes that would be needed to support this practice of a dynamic schema evolution, it seems that setting up a few extra columns should not be that big a deal.

JSON Columns and Indexing Example

Just to illustrate how it would work out, let's try and setup a table to store JSON documents. For this example, I'm looking at the Stackexchange datasets. There are many such datasets for various topic, and I'm looking at the one for math.stackexchange.com because it has a decent size - 873MB. Each of these archives comprises 8 xml files, and I'm using the Posts.xml file. One post document might look like this:

<row 
  Id="1" 
  PostTypeId="1" 
  AcceptedAnswerId="9"
  CreationDate="2010-07-20T19:09:27.200" 
  Score="85" 
  ViewCount="4121" 
  Body="&lt;p&gt;Can someone explain to me how there can be different kinds of infinities?&lt;/p&gt;" 
  OwnerUserId="10" 
  LastEditorUserId="206259" 
  LastEditorDisplayName="user126" 
  LastEditDate="2015-02-18T03:10:12.210" 
  LastActivityDate="2015-02-18T03:10:12.210" 
  Title="Different kinds of infinities?" 
  Tags="&lt;set-theory&gt;&lt;intuition&gt;&lt;faq&gt;" 
  AnswerCount="10" 
  CommentCount="1" 
  FavoriteCount="28"
/>

I'm using Pentaho Data Integration to read these files and to convert them into JSON documents. These JSON documents look like this:

{
  "Id": 1,
  "Body": "<p>Can someone explain to me how there can be different kinds of infinities?<\/p>",
  "Tags": "<set-theory><intuition><faq>",
  "Score": 85,
  "Title": "Different kinds of infinities?",
  "PostTypeId": 1,
  "AnswerCount": 10,
  "OwnerUserId": 10,
  "CommentCount": 1,
  "CreationDate": "2010-07-20 19:09:27",
  "LastEditDate": "2015-02-18 03:10:12",
  "AcceptedAnswerId": 9,
  "LastActivityDate": "2015-02-18 03:10:12",
  "LastEditorUserId": 206259
}

Initially, let's just start with a simple table called posts with a single JSON column called doc:

CREATE TABLE posts (
  doc JSON
);

After loading, I got a little over a million post documents in my table:

mysql> select count(*) from posts;
+----------+
| count(*) |
+----------+
|  1082988 |
+----------+
1 row in set (0.66 sec)

(There are actually some 5% more posts in the stackexchange data dump, but my quick and dirty transformation to turn the XML into JSON led to a bunch of invalid JSON documents, and I didn't bother to perfect the transformation enough to get them all. A million is more than enough to illustrate the approach though.)

Now, let's find the post with Id equal to 1:

mysql> select doc from posts where json_extract(doc, '$.Id') = 1
    -> \G
*************************** 1. row ***************************
doc: {"Id": 1, "Body": ">p<Can someone explain to me how there can be different kinds of infinities?</p>", "Tags": "<set-theory><intuition><faq>", "Score": 85, "Title": "Different kinds of infinities?", "PostTypeId": 1, "AnswerCount": 10, "OwnerUserId": 10, "CommentCount": 1, "CreationDate": "2010-07-20 19:09:27", "LastEditDate": "2015-02-18 03:10:12", "AcceptedAnswerId": 9, "LastActivityDate": "2015-02-18 03:10:12", "LastEditorUserId": 206259}
1 row in set (1.45 sec)

Obviously, the query plan requires a full table scan:

mysql> explain select doc from posts where json_extract(doc, '$.Id') = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | posts | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1100132 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

First, let's try and add a generated column for the Id. The Id is, as its name implies, unique, and it seems sensible to create a PRIMARY KEY for that as well:

mysql> ALTER TABLE posts
    -> ADD id INTEGER UNSIGNED
    -> GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.Id'))
    -> STORED
    -> NOT NULL PRIMARY KEY;
Query OK, 1082988 rows affected (36.23 sec)
Records: 1082988  Duplicates: 0  Warnings: 0

You might notice that in this case, the generated column is STORED rather than VIRTUAL. This is the case because MySQL won't let you create a PRIMARY KEY on a VIRTUAL generated column. If you try it anyway, you'll get:

mysql> ALTER TABLE posts
    -> ADD id INTEGER UNSIGNED
    -> GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.Id')) NOT NULL
    -> VIRTUAL
    -> PRIMARY KEY;
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

Now, let's try our -modified- query again:

mysql> explain select doc from posts where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | posts | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

If you actually try to run the query you'll notice it returns instantly - as is to be expected, since we can now access the document directly via the PRIMARY KEY.

Now, let's try this again but using a VIRTUAL column and a UNIQUE index:

mysql> ALTER TABLE posts
    -> DROP COLUMN id
    -> ;
Query OK, 1082988 rows affected (35.44 sec)
Records: 1082988  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE posts
    -> ADD id INTEGER UNSIGNED
    -> GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.Id'))
    -> VIRTUAL
    -> NOT NULL UNIQUE;
Query OK, 1082988 rows affected (36.61 sec)
Records: 1082988  Duplicates: 0  Warnings: 0

Now the plan is:

mysql> explain select doc from posts where id = 1;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | posts | NULL       | const | id            | id   | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

The plan is almost the same, except of course that now access if via the UNIQUE key rather than the PRIMARY KEY. The query again returns almost instantly, although it will be slightly slower.

That said, this example is not so much about making a benchmark or measuring performance, it's more about showing how to achieve some form of indexing when storing JSON documents in a MySQL table. I truly hope someone else will try and conduct a serious benchmark so that we can get an idea just how performance of the MySQL JSON type compares to alternative solutions (like the PostgreSQL JSON type, and MongoDB). I feel I lack both the expertise and the tools to do so myself so I'd rather leave that to experts.

Daniël van Eeden kindly pointed out that query results maybe different depending in the presence of an index. Please read bug 76834 to learn how this may affect you.

In Conclusion

• MySQL JSON support looks pretty complete.
• Integration of JSON type system and MySQL native type system is, in my opinion, pretty good, but there are definitely gotcha's.
• Achieving indexing for JSON columns relies on a few specific workarounds, which may or may not be compatible with your requirements.

I hope this post was useful to you. I sure learned a lot by investigating the feature, and it gave me a few ideas of how I could use the JSON features in the future.