Add JSON Data Type

TheColorRed's profile image TheColorRed posted 8 years ago in Feature discussion Permalink
Now that MySQL supports the JSON data type:
https://dev.mysql.com/doc/refman/5.7/en/json.html

It would be nice if HS supported that when creating a table, and when displaying the data.
TheColorRed's profile image TheColorRed posted 8 years ago Permalink
Also the ability to add secondary indexes on virtual columns when creating a table:
https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-secondary-indexes-virtual-columns
ansgar's profile image ansgar posted 8 years ago Permalink
Agree. Seems quite simple to me, as JSON fields can be handled like ordinary text fields in any data grid. The client can, but does not need to provide some tree like editor for JSON objects and arrays.
TheColorRed's profile image TheColorRed posted 8 years ago Permalink
Also when updating items they are handled as strings as well. While testing I noticed that HS doesn't handle them as strings (so that would need to be changed also).

It attempted to do this (throws a mysql error):
update table set c={"one":"1","two"2} where id=1;


when it should have done this (note the apostrophes around the json):
update table set c='{"one":"1","two"2}' where id=1;
TheColorRed's profile image TheColorRed posted 8 years ago Permalink
Also, while I was testing I created a table like this:

CREATE TABLE jemp (
c JSON,
g INT GENERATED ALWAYS AS (JSON_EXTRACT(c, '$.id')),
INDEX i (g)
);


and when I went to "Show Create Table" it output this:

CREATE TABLE jemp (
c NULL,
g INT NULL,
INDEX i (g)
);
Code modification/commit from ansgarbecker, 8 years ago, revision 9.3.0.5010
Add basic support for MySQL's new JSON data type. See http://www.heidisql.com/forum.php?t=19870
ansgar's profile image ansgar posted 8 years ago Permalink
r5010 adds basic support for JSON columns.
TheColorRed's profile image TheColorRed posted 8 years ago Permalink
Cool, it looks like basic support is working for JSON in that revision!

Issues that could be addressed at a later time:

- When updating a row An error displays: "0 rows updated when should have been 1"
- Show create table shows invalid creation code
ansgar's profile image ansgar posted 8 years ago Permalink
That "0 rows updated" error shows up only on tables without a primary key, doesn't it? Means, HeidiSQL has to take all columns into the UPDATE..WHERE clause, including the JSON column, and this fails mostly.
ansgar's profile image ansgar posted 8 years ago Permalink
Virtual columns with that GENERATED ALWAYS should now show up correctly, with r5013.
TheColorRed's profile image TheColorRed posted 8 years ago Permalink
When I run the command show create table xxx; this is displayed:
CREATE TABLE `jemp` (
`c` json DEFAULT NULL,
`g` int(11) GENERATED ALWAYS AS (JSON_EXTRACT(c, '$.id')) VIRTUAL,
KEY `i` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


When I view through the tab in HeidiSQL, I get this:
CREATE TABLE `jemp` (
`c` JSON NULL DEFAULT NULL,
`g` INT(11) AS (JSON_EXTRACT(c, '$.id')) VIRTUAL,
INDEX `i` (`g`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;


Is HeidiSQL re-writing it or is syntax highlighting removing it because of the keywords?
ansgar's profile image ansgar posted 8 years ago Permalink
HeidiSQL parses that SHOW CREATE TABLE code and recreates it from the scratch for the CREATE CODE tab in the table editor.

Please login to leave a reply, or register at first.