Why is mysql JSON_OBJECT variable assignment inconsistent?

Question

Can someone help explain the following behavior? As near as I can tell, it looks like when a JSON object is assigned to a mysql variable, it may or may not be escaped depending upon the context.

So let's try it

mysql> CREATE TABLE `json_test` (`jdata` json DEFAULT NULL);
Query OK, 0 rows affected (0.09 sec)

mysql> SET @IV_PROP = JSON_OBJECT( "name", "supportsInvalidation", "type", "java.lang.Boolean", "privy", false);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO json_test (jdata) VALUES(@IV_PROP);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM json_test;
+-------------------------------------------------------------------------------+
| jdata                                                                         |
+-------------------------------------------------------------------------------+
| {"name": "supportsInvalidation", "type": "java.lang.Boolean", "privy": false} |
+-------------------------------------------------------------------------------+
1 row in set (0.01 sec)

That looks cool to me. But then....

mysql> UPDATE json_test SET jdata = JSON_SET(jdata, "$", @IV_PROP);
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM json_test;
+-------------------------------------------------------------------------------------------+
| jdata                                                                                     |
+-------------------------------------------------------------------------------------------+
| "{\"name\": \"supportsInvalidation\", \"type\": \"java.lang.Boolean\", \"privy\": false}" |
+-------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Much sadness, but to fix it

mysql> UPDATE json_test SET jdata = JSON_SET(jdata, "$", JSON_OBJECT( "name", "supportsInvalidation", "type", "java.lang.Boolean", "privy", false));
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM json_test;
+-------------------------------------------------------------------------------+
| jdata                                                                         |
+-------------------------------------------------------------------------------+
| {"name": "supportsInvalidation", "type": "java.lang.Boolean", "privy": false} |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So that kind of undermines the value of a variable, no?

Answer 1

It is not inconsistent. The JSON_SET() function does not merge JSON documents, it only sets a scalar value in a JSON document. Despite the fact that your string is in JSON document format, using JSON_SET() treats it as a scalar value (a string).

You don't need to use JSON_SET() in your example. You can just use assignment.

mysql> insert into json_test() values (); -- a row with NULL

mysql> UPDATE json_test SET jdata = @IV_PROP;

mysql> select * from json_test;
+-------------------------------------------------------------------------------+
| jdata                                                                         |
+-------------------------------------------------------------------------------+
| {"name": "supportsInvalidation", "type": "java.lang.Boolean", "privy": false} |
+-------------------------------------------------------------------------------+

If you want to merge a variable into an existing JSON document, you would use JSON_MERGE_PATCH() or JSON_MERGE_PRESERVE().

---

You wrote a comment with this example:

UPDATE json_test SET jdata = JSON_UNQUOTE(JSON_SET(jdata, "$", @IV_PROP));

This eventually does what you want, but it's not necessary. In that code, JSON_SET() produces a JSON document consisting of a single string (a JSON document doesn't have to be an array or an object, it can be simply a single JSON value at the root level of the document). The string itself contains JSON syntax. Then you pass that to JSON_UNQUOTE(), removing the escape characters as if the result were a JSON document literal.

The following does the same thing without the intermediate steps:

UPDATE json_test SET jdata = @IV_PROP;

By analogy, you could set an integer column like this:

UPDATE int_test SET val = (100 * 42) / 100;

But why? You should just do it this way:

UPDATE int_test SET val = 42;

---

Regarding your use of JSON_OBJECT(), the following also works:

mysql> UPDATE json_test SET jdata = JSON_SET(jdata, '$', CAST(@IV_PROP AS JSON));

mysql> select * from json_test;
+-------------------------------------------------------------------------------+
| jdata                                                                         |
+-------------------------------------------------------------------------------+
| {"name": "supportsInvalidation", "type": "java.lang.Boolean", "privy": false} |
+-------------------------------------------------------------------------------+

So it appears that JSON_SET() does "merge" JSON structure, but only if the expression has the JSON document type. A MySQL user-defined variable cannot have a JSON type.

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html says:

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. ... A value of a type other than one of the permissible types is converted to a permissible type. ... A value having the JSON data type is converted to a string with a character set of utf8mb4 and a collation of utf8mb4_bin.

(emphasis mine)

So you can assign a JSON literal to a user variable, and it becomes a string. You can use a value in JSON_SET(), and a string value will be interpreted as a string scalar to JSON. A user variable can only be a string scalar value, but you use a CAST() expression to convert it from string back to JSON value.