MySQL write empty json in column of JSON type

Question

How to override a column of JSON type that has value = NULL to empty json, {}?

UPDATE `table`
SET `column_json` = JSON_SET(NULL, '{}', '{}')
WHERE
    `condition` = 1;

Madhur Bhaiya · Accepted Answer · 2019-09-16 13:24:15Z

3

I think you can simply check for IS NULL in the WHERE clause, and then set the JSON column to string {}. MySQL should be able to implicitly typecast the string literal (which is also a valid JSON literal) to JSON:

UPDATE `table`
SET `column_json` = '{}'
WHERE
    `condition` = 1 
     AND `column_json` IS NULL

edited Sep 16, 2019 at 13:24

answered Sep 16, 2019 at 13:22

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

And this should work because a string literal should also be a valid JSON literal.

GMB Over a year ago

Hey Madhur... congratulations for earning the mysql gold badge!

Collectives™ on Stack Overflow

MySQL write empty json in column of JSON type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related