MySQL/MariaDB: Add value to array

-- make "external" an JSON object if null
UPDATE node_data
JOIN nodes ON nodes.id = node_data.node_id
SET external = "{}"
WHERE
  node_data.external IS NULL
  AND nodes.available_for_rent = 1;
 
-- add "export_portals" array to "external" if it does not exist
UPDATE node_data
JOIN nodes ON nodes.id = node_data.node_id
SET external = JSON_SET(external, '$.export_portals', JSON_ARRAY())
WHERE
  NOT JSON_CONTAINS_PATH(external, 'all', '$.export_portals')
  AND nodes.available_for_rent = 1;
 
-- add "hpm-api" value to external.export_portals
UPDATE node_data
JOIN nodes ON nodes.id = node_data.node_id
SET external = JSON_ARRAY_APPEND(external, '$.export_portals', 'hpm-api')
WHERE
  NOT JSON_CONTAINS(external, '["hpm-api"]', '$.export_portals')
  AND nodes.available_for_rent = 1;
 

UPDATE node_data
JOIN nodes ON nodes.id = node_data.node_id
SET external = JSON_REMOVE(external, JSON_UNQUOTE(JSON_SEARCH(external, 'one', 'hpm-api', NULL, '$.export_portals')))
WHERE
  JSON_CONTAINS(external, '["hpm-api"]', '$.export_portals')
  AND nodes.available_for_rent = 1;
 
UPDATE node_data
JOIN nodes ON nodes.id = node_data.node_id
SET external = JSON_REMOVE(external, '$.export_portals')
WHERE
  external->'$.export_portals' = JSON_ARRAY()
  AND nodes.available_for_rent = 1;
 
UPDATE node_data
JOIN nodes ON nodes.id = node_data.node_id
SET external = NULL
WHERE
  external = JSON_OBJECT()
  AND nodes.available_for_rent = 1;