How to deal with MySQL JSON data type?

How to deal with MySQL JSON data type?

pisislerpisisler Posts: 106Questions: 21Answers: 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: 61,446Questions: 1Answers: 10,054 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: 106Questions: 21Answers: 1
    edited April 2022

    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: 61,446Questions: 1Answers: 10,054 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: 106Questions: 21Answers: 1

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

  • mguinnessmguinness Posts: 85Questions: 12Answers: 1
    edited January 2023

    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).

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • mguinnessmguinness Posts: 85Questions: 12Answers: 1

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

  • OSFOSF Posts: 18Questions: 0Answers: 0

    Any news for Datatype JSON ?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • pisislerpisisler Posts: 106Questions: 21Answers: 1
    edited February 2023

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • OSFOSF Posts: 18Questions: 0Answers: 0
    edited March 2023

    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) {
            ...
        };
    }
    ...
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • OSFOSF Posts: 18Questions: 0Answers: 0

    Hi @allan,

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

Sign In or Register to comment.