CODEWITHSUNDEEP
mysqlmysql-8.0
the_nuts
the_nuts

Reputation: 6054

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=?;

Upvotes: 3

Views: 11721

Answers (4)

Kai
Kai

Reputation: 73

This stumped me as well for quite awhile but try using JSON_ARRAY() instead of the double bracket notation '[]'. For example:

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

Upvotes: 7

the_nuts
the_nuts

Reputation: 6054

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;

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use CASE .. WHEN conditional operator to handle this in single query. Also, since your datatype for settings is Json, you will need to Cast() it to string (Char) first for checking []:

SELECT *, 
       CASE WHEN CAST(settings AS CHAR) = '[]'  
            THEN JSON_SET('{}', '$.newFoo', 10)
            ELSE JSON_SET(settings, '$.newFoo', 10) 
       END AS modified_settings 
FROM test;

Update query for the same would be as follows:

UPDATE test 
SET settings = CASE WHEN CAST(settings AS CHAR) = '[]'  
                    THEN JSON_SET('{}', '$.newFoo', 10)
                    ELSE JSON_SET(settings, '$.newFoo', 10) 
               END

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

The reason your call to JSON_SET is not behaving as you expect is that the syntax you need to work with JSON arrays differs from what you need to work with individual JSON. Consider the following query, which adds a JSON to an empty array:

SELECT JSON_SET('[]', '$[0]', '{"newFoo": 10}') AS output;

This prints:

["{\"newFoo\": 10}"]

Demo

So the JSON_SET function appears to have two behaviors to it. When operating on JSON proper, it can either insert new keys or update keys which already exist. When operating on arrays, it can insert/update elements of the array, which might be entire JSON objects.

Upvotes: 1

Related Questions