|
If a BIT(1) field is put into various JSON-functions, e.g. JSON_OBJECT, the charset of the JSON is inconsequent and it returns invalid JSON.
Example script:
CREATE TABLE t1(text_field VARCHAR(50), bool_field BIT(1) NOT NULL DEFAULT 0);
|
INSERT INTO t1(text_field, bool_field) VALUES ('Some Umlauts with TRUE: äöü', 1), ('Also some Umlauts, but with FALSE: äöü', 0);
|
SELECT JSON_OBJECT('text_field', text_field, 'bool_field', bool_field) FROM t1;
|
Output:
|
+--------------------------------------------------------------------------------+
|
| json_object('text_field', text_field, 'bool_field', bool_field) |
|
+--------------------------------------------------------------------------------+
|
| {"text_field": "Some Umlauts with TRUE: ���", "bool_field": \u0001} |
|
| {"text_field": "Also some Umlauts, but with FALSE: ���", "bool_field": \u0000} |
|
+--------------------------------------------------------------------------------+
|
Workaround
SELECT JSON_OBJECT('text_field', text_field, 'bool_field', CASE WHEN bool_field THEN true ELSE false END) FROM t1;
|
Output:
+----------------------------------------------------------------------------------------------------+
|
| json_object('text_field', text_field, 'bool_field', CASE WHEN bool_field THEN true ELSE false END) |
|
+----------------------------------------------------------------------------------------------------+
|
| {"text_field": "Some Umlauts with TRUE: äöü", "bool_field": true} |
|
| {"text_field": "Also some Umlauts, but with FALSE: äöü", "bool_field": false} |
|
+----------------------------------------------------------------------------------------------------+
|
2 rows in set (0.001 sec)
|
|