How to deal with MySQL JSON data type?

pisisler

Hi all.

As you know, newer versions of MySQL support a new data type which is JSON, which lets you store json objects in a cell. Of course with some functions to make JSON operations right in the database.

Consider the data in the DB be like;

order_id | order_number | buyer_info
12345 | O54 | {"name": "John", "surname": "Doe", "address": "Maine St.", "phone": {"areaCode": 123, "number": 56789}}

Does Editor support reading and writing to this database column? Or we have to make a load of manipulations before read and write?

allan

Hi,

Sorry for the delay in replying here - I missed this one. No - unfortunately not at this time. The syntax used for escaping identifiers is what will trip up the Editor server-side libraries.

Allan

pisisler

So any ideas on how to deal with them? Like for example manipulate data before table read and before data update by decoding JSON into fields?

allan

It might be possible to still use them, but I think there would be some conditions and constraints depending on what the data is and if you want to use all of the fields from inside the JSON, etc.

For example, if it were a JSON object with a latitude and longitude then a custom field type would be the way to go - just feed it the whole JSON object and expect it to return a JSON object.

If however you wanted to edit individual properties inside the JSON object, then the best option for that would be to use a single field on the server-side, which will get the whole JSON object, and on the client-side pick out the parts you want, just like you would with normal Javascript dotted object notation. e.g. json.firstName.

The tricky bit with that I think will come with the submission of the data. First you would need to make sure you submit all of the properties from the JSON object, with their values, not just the one being edited, that is because the Editor server-side libraries won't do any kind of merge on the JSON object automatically. You'll probably also need to use a setFormatter to encode it as a JSON object.

Regards,
Allan

pisisler

Thank you for the valuable information. If I come to that point, I might resurrect this topic from time to time

mguinness

Bumping this topic as I think that this will become more relevant since the major RDBMS like MSSQL, PostgreSQL, MySQL support JSON columns. Another scenario is that you might have a field with a JSON object array that could potentially be edited in conjunction with the datatable field type (see Editor 2 nested editing for a discussion).

allan

Thanks! I've filed it as an issue to be looked at. It won't make it for the current 2.1 development (which is nearly finished), but I will look into it after that.

Allan

mguinness

Appreciate the feedback Allan, glad to hear that you will evaluate in due course.

OSF

Any news for Datatype JSON ?

allan

Not yet - sorry. Out of interest, how will you be editing the JSON data? Or are you looking to pick out specific pieces of data from the JSON, and edit just them - i.e. treat a JSON property as if it were an SQL column?

Allan

pisisler

Is it any possible to show that kind of data properly in Datatables? (I mean not the Editor.)

To answer the question above:

I have a service collecting orders of customers from different platforms and standardize them into JSON documents stored in MySQL. It would be useful for me to manipulate order info manually when needed.

allan

Yes, you can display virtually anything in the DataTable. What is the JSON being returned for each row, and what data do you want to have displayed in the cells?

Allan

OSF

Hi @allen,

thanks for answere.

To display data in datatables column,i think its a good starting Point to select only a single value from JSON Field.

Let me expect what i mean:

MySQL [PLAYGROUND]> show columns from JSON_TEST;
+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| Attributes | json | YES  |     | NULL    |       |
+------------+------+------+-----+---------+-------+
1 row in set (0.026 sec)


MySQL [PLAYGROUND]> select * from JSON_TEST;
+------------------------------------------------+
| Attributes                                     |
+------------------------------------------------+
| {"age": 30, "car": "mercedes", "name": "John"} |
+------------------------------------------------+
1 row in set (0.001 sec)


MySQL [PLAYGROUND]> select Attributes->'$.car' AS car from JSON_TEST;
+------------+
| car        |
+------------+
| "mercedes" |
+------------+
1 row in set (0.004 sec)

As Brainstorming for datatables columns:

...
{
 data: "Attributes->car", // The json result should be 'car' (SELECT Attributes->'$.car' AS car ...)
 render: function (data, type, full, meta) {
    if (full.car) {
        ...
    };
},{
 data: "Attributes->age", // The json result should be 'age' (SELECT Attributes->'$.age' AS age ...)
 render: function (data, type, full, meta) {
    if (full.age) {
        ...
    };
}
...

More complex brainstorming

...{
 data: "Attributes->foo->bar", // (SELECT Attributes->‘$.foo‘->‘$.bar‘ AS bar ...)
 render: function (data, type, full, meta) {
    if (full.bar) {
        ...
    };
}
...

allan

That should be usable at the moment if you were to search the Attributes column. If you do so, what is the JSON that is returned from the server? I expect I'll be able to show how it could be displayed.

Allan

OSF

Hi @allan,

i don't know, what you mean exactly...