Data truncation: ” While Inserting or Updating Value

I am trying to Insert/update a string (JSON). While inserting/Updating the value I get data truncation error.

I have tried making a JSON type column and passing an JSON_OBJECT() type but that fails as well.

select '''[{"id":"1202","title":"Asian","notes":"","active":"1"}]''';

CREATE TABLE mktesttable (
    id int NOT NULL,
    s VARCHAR(34530) NOT NULL
);

INSERT INTO mktesttable
select 1, '''[{"id":"1202","title":"Asian","notes":"","active":"1"}]''';

select *  from mktesttable;

// That Works

---

INSERT INTO mktesttable
SELECT 
    patient_data.id, 
    CONCAT(
        '''[{"id":"',   patient_data.race,
        '","title":"',  list_options.title, 
        '","notes":"',  list_options.notes, 
        '","active":"', list_options.active,
        '"}]'''
    ) as s
FROM 
    patient_data 
    INNER JOIN list_options 
        ON patient_data.race = list_options.id order by 1 desc

Yields same result (Id’s and data varies) but doesn’t work

Result Set

Answer

If you want to store JSON object, you should use the JSON datatype instead of strings. To create a JSON object, you can use JSON_OBJECT.

CREATE TABLE mktesttable (
    id int NOT NULL,
    s JSON NOT NULL
);

INSERT INTO mktesttable
SELECT 
    patient_data.id, 
    JSON_OBJECT(
        'id',     patient_data.race,
        'title',  list_options.title, 
        'notes',  list_options.notes, 
        'active', list_options.active,
    )
FROM 
    patient_data 
    INNER JOIN list_options 
        ON patient_data.race = list_options.id 
ORDER BY patient_data.id desc

If you need a JSON array as shown in your sample data, then :

    JSON_ARRAY(
        JSON_OBJECT(
            'id',     patient_data.race,
            'title',  list_options.title, 
            'notes',  list_options.notes, 
            'active', list_options.active,
        )
    )