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

Barmar · Accepted Answer · 2019-12-30 18:08:17Z

1

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'

answered Dec 30, 2019 at 18:08

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

JSON_ARRAY_APPEND() reject if target value is null Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related