json crud on mariaDB-CodePudding

i have column in mariaDB with this format

[
  {
   "id":287,
   "date_created":"2021-10-23",
   "resume":"abcde"
  },{   
   "id":300,
   "date_created":"2021-10-23",
   "resume":"fghijkl"
  }
]

how is the query to insert (append), edit and delete the record?

CodePudding user response：

You'd probably be better off splitting that into a separate table but if you are stuck with this, you'll have to update the whole JSON using the MariaDB JSON functions.

With the following table:

CREATE TABLE my_table(data JSON);
INSERT INTO my_table VALUES ('[{"id":287,"date_created":"2021-10-23","resume":"abcde"},{"id":300,"date_created":"2021-10-23","resume":"fghijkl"}]');

The following operations can be done:

-- Append new value to the array
UPDATE my_table SET data = JSON_ARRAY_APPEND(data, '$',
  JSON_OBJECT('id', 123, 'date_created', '2021-10-23', 'resume', 'asdfqwer')
);

-- Remove the first value from the array
UPDATE my_table SET data = JSON_REMOVE(data, '$[0]');

-- Modify the first value in the array
UPDATE my_table SET data = JSON_SET(data, '$[0].id', 321);

Note: When inserting new values into an existing JSON value, there are times when you have to "convert" it to a JSON object using JSON_EXTRACT with the path set to $. One such example is if we were to use JSON_ARRAY_APPEND with a literal JSON string instead of the JSON_OBJECT function:

UPDATE my_table SET data = JSON_ARRAY_APPEND(data, '$',
  JSON_EXTRACT('"id": 123, "date_created": "2021-10-23", "resume": "asdfqwer"', '$')
);

If the raw literal JSON string was appended, you'd end up with a JSON string being appended instead of a JSON object.