Search code examples
mysqlmysql-json

MySQL JSON column add new array as element

I have a table with JSON type column, I want to update a column with new array element in existing JSON.

What needs to be done: add an array in JSON column when employee punch_in and add another array in JSON column when employee punch_out.

{"emp_sheet":[{"rulecode":"PUNCH_IN","result":1,"applytime":"2018-04-12 04:50:39"},{"rulecode":"PUNCH_OUT","result":1,"applytime":"2018-04-12 13:01:39"}]}

What I did, for employee punch_in:

UPDATE table 
SET rule_codes = JSON_SET(COALESCE(rule_codes, '{}'), '$.emp_sheet', '{"rulecode":"PUNCH_IN","result":1,"applytime":"2018-04-12 04:50:39"}') 
WHERE emp_id = 1

Result in rule_codes column =

{"emp_sheet": "{"rulecode":"PUNCH_IN","result":1,"applytime":"2018-04-12 04:50:39"}"}

Please help me to write update query for employee punch_out.

Solution

  • Try to use JSON_ARRAY_APPEND instead of JSON_SET.

    Manual - https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

    I think it could be like this

    rule_codes = JSON_ARRAY_APPEND(COALESCE(rule_codes, '{"emp_sheet":[]}'), '$.emp_sheet', '{"rulecode":"PUNCH_IN","result":1,"applytime":"2018-04-12 04:50:39"}')

    or

    rule_codes = IF(rule_codes IS NULL,'
    '{"emp_sheet":[{"rulecode":"PUNCH_IN","result":1,"applytime":"2018-04-12 04:50:39"}]}',
    JSON_ARRAY_APPEND(rule_codes, '$.emp_sheet', '{"rulecode":"PUNCH_IN","result":1,"applytime":"2018-04-12 04:50:39"}')
  )