MySQL JSON column add new array as element

Question

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.

Nick · Accepted Answer · 2018-04-29 01:46:40Z

This would be easiest if you made $.emp_sheet a JSON array on punch in:

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

Then on punch out, you can add another element to the array:

UPDATE table3
SET rule_codes = JSON_SET(COALESCE(rule_codes, JSON_OBJECT('emp_sheet', JSON_ARRAY())),
                          '$.emp_sheet[1]',
                          '{"rulecode":"PUNCH_OUT","result":1,"applytime":"2018-04-12 13:01:39"}') 
WHERE emp_id = 1;

SELECT rule_codes FROM table3 WHERE emp_id = 1

Output:

{"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\"}"
 ]}

Note that when you do the SET, the input JSON ('{"rulecode ... }') gets treated as a string, hence the escaped " in the output above. You can remove those with JSON_UNQUOTE when you extract i.e.

SELECT JSON_UNQUOTE(JSON_EXTRACT(rule_codes, '$.emp_sheet[0]')) FROM `table3`

or using the short-cut notation

SELECT rule_codes->>'$.emp_sheet[0]' FROM `table3`

Output:

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

Sergey Menshov · Accepted Answer · 2018-04-28 08:28:44Z

0

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"}')
  )

edited Apr 28, 2018 at 8:28

answered Apr 28, 2018 at 8:22

Sergey Menshov

3,9062 gold badges11 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySQL JSON column add new array as element

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related