5

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.

Ayan
  • 8,192
  • 4
  • 46
  • 51

1 Answers1

11

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • my liked_ids is initially null.How to enforce it to be an array by default, while creating the table,so that your above solution works? – Ayan Mar 04 '18 at 11:08
  • 4
    Too bad, that `JSON_ARRAY_APPEND` doesn't like empty arrays. But you could use something like `COALESCE(JSON_ARRAY_APPEND(liked_Ids, '$', 5), JSON_ARRAY(5))` instead of the CASE statement. – Paul Spiegel Mar 04 '18 at 12:20
  • 1
    Field has to be Null, like really null, with `NULL` keyword in it, not just empty. I've been struggling for an hour because of that. :( – Dohab Sep 13 '19 at 16:50