how to push an element to a JSON in MySQL

Question

I am trying to push a new element to a JSON array in MySQL.

ex. in following table People:

id(int)     liked_ids(JSON)
   1              [2,3]

Now I want to push 5 into liked_ids.

I had a look at JSON_ARRAY_APPEND but couldn't figure out a way to use it even from the docs.

Answer 1

You need to use:

UPDATE tab
SET liked_ids = JSON_ARRAY_APPEND (liked_Ids, '$', 5)
WHERE id = 1;

DBFiddel Demo

EDIT:

my liked_ids is initially null.How to enforce it to be an array by default while creating the table?

You could try CASE expression:

UPDATE tab
SET liked_ids = CASE WHEN liked_ids IS NULL THEN '[5]'
                ELSE  JSON_ARRAY_APPEND (liked_Ids, '$', 5)
                END
WHERE id = 1;

--or as @Paul Spiegel mentioned
UPDATE tab
SET liked_ids = COALESCE(JSON_ARRAY_APPEND(liked_Ids, '$', 5), JSON_ARRAY(5))
WHERE id = 1;

DBFiddle Demo2