Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
-
10.2.12, 10.2.14
Description
JSON data is not getting recognized, so statements like the following result in broken JSON-strings. In MySQL it doesn't happen because JSON is recognized as a datatype. If adding JSON as a datatype is not an option to consider, then maybe add an optional parameter to "JSON_OBJECT()" to check the passed attributes to not escape them again. When advertising JSON-support in MariaDB 10.2 results in generating those strings manually via "CONCAT()" is not what people expect and this is the only reason I can't use MariaDB in nearly all of my projects.
Note: If testing in MySQL 5.7, cast the concatenated JSON-value of "Permission" as JSON manually, so it wont be escaped again.
SELECT
|
JSON_OBJECT(
|
'Staff', JSON_OBJECT(
|
'Staff_ID', Staff_ID,
|
'StaffNumber', Staff_Number,
|
'StaffName', Staff_Name
|
),
|
'Company', JSON_OBJECT(
|
'Company_ID', Company_ID,
|
'Description', Company_Name
|
),
|
'Permissions', CONCAT(
|
'[',
|
GROUP_CONCAT(
|
JSON_OBJECT(Permission_ID, Permission_Flag)
|
),
|
']'
|
)
|
) AS StaffObj
|
FROM Staff
|
NATURAL JOIN Company
|
NATURAL JOIN Staff_Permissions
|
GROUP BY Staff_ID;
|
Attachments
Issue Links
- relates to
-
MDEV-27018 IF and COALESCE lose "json" property
- Closed
-
MDEV-26506 Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT
- Closed
I found a workaround:
delimiter ;;
delimiter ;
call json_bug_workaround();
Results in what I expect:
+---------------------+--------------+--------------+--------------+
| a | json_type(a) | b | json_type(b) |
+---------------------+--------------+--------------+--------------+
| {"k": {"kk": "vv"}} | OBJECT | {"kk": "vv"} | OBJECT |
+---------------------+--------------+--------------+--------------+
This means that json_extract( value, '$' ) is kind of casting the value to the json thing
it is, provided, it is valid json - but in case value is declared as json this is always the case.
If value is not valid json, then the result of this kind of cast is NULL for mariadb and
mysql will throw an error in this case - so be careful if you need to write your sql for both!