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