MySQL JSON_SET() to work with empty arrays

Question

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;

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

Have you tried using the empty array notation for json? ('3', '{}'); — Erik Baars
– Erik Baars, Commented Jul 16, 2019 at 15:00

Kai · Accepted Answer · 2019-11-29 20:14:30Z

7

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());

answered Nov 29, 2019 at 20:14

Kai

731 silver badge4 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2019-07-16 15:13:02Z

1

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.

answered Jul 16, 2019 at 15:13

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

the_nuts · Accepted Answer · 2019-09-27 12:27:22Z

1

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;

answered Sep 27, 2019 at 12:27

the_nuts

6,1443 gold badges44 silver badges75 bronze badges

Comments

Madhur Bhaiya · Accepted Answer · 2019-07-16 15:13:10Z

0

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

edited Jul 16, 2019 at 15:13

answered Jul 16, 2019 at 15:05

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

1 Comment

the_nuts Over a year ago

Actually I want to make an UPDATE not a select, the select was just to show the result

Collectives™ on Stack Overflow

MySQL JSON_SET() to work with empty arrays

4 Answers 4

Comments

Demo

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related