JSON_ARRAY_APPEND() reject if target value is null Mysql

Question

Here is Scenario, Adding the user-id's to the user_details. what if external_id does not present in the profile table and it returns null (select statement returns null).

How does JSON_ARRAY_APPEND reject the query and does not add to the user_details JSON colum.

UPDATE column1 SET user_details= JSON_SET(user_details, "$.ids", IFNULL(user_details->'$.ids',JSON_ARRAY())),
user_details= JSON_ARRAY_APPEND(user_details, "$.ids", (Select id from column2 where external_id='999999999999999'))
where id = 880;

Thanks

score 1 · Accepted Answer · answered Dec 30 '19 at 18:08

Check for this in the WHERE clause of the UPDATE statement.

UPDATE column1 SET user_details= JSON_SET(user_details, "$.ids", IFNULL(user_details->'$.ids',JSON_ARRAY())),
user_details= JSON_ARRAY_APPEND(user_details, "$.ids", (Select id from column2 where external_id='999999999999999'))
where id = 880
AND EXISTS (Select id from column2 where external_id='999999999999999')

Or use a JOIN rather than nesting a subquery.

UPDATE column1 AS c1
CROSS JOIN column2 AS c2
SET user_details= JSON_SET(c1.user_details, "$.ids", IFNULL(c1.user_details->'$.ids',JSON_ARRAY())),
    c1.user_details= JSON_ARRAY_APPEND(c1.user_details, "$.ids",c2.id)
WHERE c1..id = 880
AND c2.external_id = '999999999999999'

JSON_ARRAY_APPEND() reject if target value is null Mysql

1 Answers1