Search code examples
mysqlmysql-8.0

MySQL JSON_SET() to work with empty arrays

I have a table like this:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `settings` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test` (`id`, `settings`) VALUES 
    ('1', '{\"foo\": {\"bar\": 1}}'),
    ('2', '{\"foobar\": 2}'),
    ('3', '[]');

I want to add a new setting to a row, so I tried JSON_SET:

SELECT *, JSON_SET(settings, '$.newFoo', 10) FROM test;

enter image description here

As you can see, it doesn't work on the item 3. The expected result is of course the same as on line 4; [] is the result of json-encoding an empty array in php, if that item doesn't have any setting.

Can make a single query that works for all the cases?

P.S.: What I need to do is an UPDATE, i.e. UPDATE test SET settings=JSON_SET(...) WHERE id=?;

Solution

  • I solved by always storing the values as objects instead of arrays, in PHP you need to add a flag to the json_encode function:

    json_encode($value, JSON_FORCE_OBJECT)

    Or, as an alternative, make the field nullable and use COALESCE:

    SELECT *, JSON_SET(COALESCE(settings, '{}'), '$.newFoo', 10) FROM test;