Creating JSON Values
Dive deep into the creation of JSON values in MySQL.
We'll cover the following...
Instead of explicitly casting string literals that contain JSON values to JSON, we can also implicitly create data of the type JSON by using JSON values in string literals where an expression of the type JSON is expected:
-- Generate a temporary table that stores JSON valuesCREATE TEMPORARY TABLE json_value(value JSON);-- Verbose means to create a JSON array using `CAST`INSERT INTO json_value VALUES (CAST('[0, null, "A"]' AS JSON));-- Compact means to create a JSON array implicitlyINSERT INTO json_value VALUES ('[0, null, "A"]');-- Inspect the resulting tableTABLE json_value;
In the code snippet above, the INSERT statement on line 8 demonstrates the explicit creation of a JSON value, whereas the INSERT statement on line 10 utilizes implicit creation. This comes in handy if we quickly want to create a JSON value on the spot. On a side note, implicitly creating JSON values also enables the syntax highlighting of SQL editors like IntelliJ (DataGrip) to recognize JSON and highlight it properly, which is not the case using CAST. Either way, using string literals does not scale well if we require more complex JSON expressions. Hence, MySQL allows the creation of JSON objects and arrays through different functions.
Object
Besides its literal values, JSON allows the creation of complex structures like objects that combine literal values and ...