How to deal with MySQL JSON data type?

How to deal with MySQL JSON data type?

pisislerpisisler Posts: 91Questions: 19Answers: 1

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?

Answers

  • allanallan Posts: 57,236Questions: 1Answers: 9,113 Site admin

    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

  • pisislerpisisler Posts: 91Questions: 19Answers: 1
    edited April 13

    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?

  • allanallan Posts: 57,236Questions: 1Answers: 9,113 Site admin

    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

  • pisislerpisisler Posts: 91Questions: 19Answers: 1

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

Sign In or Register to comment.