{"type":"rich","version":"1.0","provider_name":"phorkie","provider_url":"https:\/\/p.cweiske.de\/","title":"MySQL\/MariaDB: Add value to array","author_name":"Christian Weiske","cache_age":86400,"width":900,"height":900,"html":"<!-- embedding all files of https:\/\/p.cweiske.de\/664 -->\n<link rel=\"stylesheet\" href=\"https:\/\/p.cweiske.de\/css\/embed.css\"\/>\n<div class=\"phork\" id=\"664\">\n    <div class=\"phork-file\">\n <div class=\"phork-content\">\n  <style type=\"text\/css\">\/**\n * GeSHi (C) 2004 - 2007 Nigel McNie, 2007 - 2014 Benny Baumann\n * (http:\/\/qbnz.com\/highlighter\/ and http:\/\/geshi.org\/)\n *\/\n.sql .de1, .sql .de2 {font: normal normal 1em\/1.2em monospace; margin:0; padding:0; background:none; vertical-align:top;}\n.sql  {font-family:monospace;}\n.sql .imp {font-weight: bold; color: red;}\n.sql li, .sql .li1 {color: #DDD;}\n.sql .ln {width:1px;text-align:right;margin:0;padding:0 2px;vertical-align:top;}\n.sql .kw1 {color: #993333; font-weight: bold;}\n.sql .co1 {color: #808080; font-style: italic;}\n.sql .coMULTI {color: #808080; font-style: italic;}\n.sql .es0 {color: #000099; font-weight: bold;}\n.sql .br0 {color: #66cc66;}\n.sql .sy0 {color: #66cc66;}\n.sql .st0 {color: #ff0000;}\n.sql .nu0 {color: #cc66cc;}\n.sql span.xtra { display:block; }\n<\/style><div class=\"code\"><table class=\"sql\"><tbody><tr class=\"li1\"><td class=\"ln\"><pre class=\"de1\">1\n2\n3\n4\n5\n6\n7\n8\n9\n10\n11\n12\n13\n14\n15\n16\n17\n18\n19\n20\n21\n22\n23\n24\n<\/pre><\/td><td class=\"de1\"><pre class=\"de1\"><span class=\"co1\">-- make &quot;external&quot; an JSON object if null<\/span>\n<span class=\"kw1\">UPDATE<\/span> node_data\n<span class=\"kw1\">JOIN<\/span> nodes <span class=\"kw1\">ON<\/span> nodes<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> node_data<span class=\"sy0\">.<\/span>node_id\n<span class=\"kw1\">SET<\/span> external <span class=\"sy0\">=<\/span> <span class=\"st0\">&quot;{}&quot;<\/span>\n<span class=\"kw1\">WHERE<\/span>\n&#160; node_data<span class=\"sy0\">.<\/span>external <span class=\"kw1\">IS<\/span> <span class=\"kw1\">NULL<\/span>\n&#160; <span class=\"kw1\">AND<\/span> nodes<span class=\"sy0\">.<\/span>available_for_rent <span class=\"sy0\">=<\/span> <span class=\"nu0\">1<\/span>;\n&#160;\n<span class=\"co1\">-- add &quot;export_portals&quot; array to &quot;external&quot; if it does not exist<\/span>\n<span class=\"kw1\">UPDATE<\/span> node_data\n<span class=\"kw1\">JOIN<\/span> nodes <span class=\"kw1\">ON<\/span> nodes<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> node_data<span class=\"sy0\">.<\/span>node_id\n<span class=\"kw1\">SET<\/span> external <span class=\"sy0\">=<\/span> JSON_SET<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> <span class=\"st0\">'$.export_portals'<\/span><span class=\"sy0\">,<\/span> JSON_ARRAY<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span>\n<span class=\"kw1\">WHERE<\/span>\n&#160; <span class=\"kw1\">NOT<\/span> JSON_CONTAINS_PATH<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> <span class=\"st0\">'all'<\/span><span class=\"sy0\">,<\/span> <span class=\"st0\">'$.export_portals'<\/span><span class=\"br0\">&#41;<\/span>\n&#160; <span class=\"kw1\">AND<\/span> nodes<span class=\"sy0\">.<\/span>available_for_rent <span class=\"sy0\">=<\/span> <span class=\"nu0\">1<\/span>;\n&#160;\n<span class=\"co1\">-- add &quot;hpm-api&quot; value to external.export_portals<\/span>\n<span class=\"kw1\">UPDATE<\/span> node_data\n<span class=\"kw1\">JOIN<\/span> nodes <span class=\"kw1\">ON<\/span> nodes<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> node_data<span class=\"sy0\">.<\/span>node_id\n<span class=\"kw1\">SET<\/span> external <span class=\"sy0\">=<\/span> JSON_ARRAY_APPEND<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> <span class=\"st0\">'$.export_portals'<\/span><span class=\"sy0\">,<\/span> <span class=\"st0\">'hpm-api'<\/span><span class=\"br0\">&#41;<\/span>\n<span class=\"kw1\">WHERE<\/span>\n&#160; <span class=\"kw1\">NOT<\/span> JSON_CONTAINS<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> <span class=\"st0\">'[&quot;hpm-api&quot;]'<\/span><span class=\"sy0\">,<\/span> <span class=\"st0\">'$.export_portals'<\/span><span class=\"br0\">&#41;<\/span>\n&#160; <span class=\"kw1\">AND<\/span> nodes<span class=\"sy0\">.<\/span>available_for_rent <span class=\"sy0\">=<\/span> <span class=\"nu0\">1<\/span>;\n&#160;<\/pre><\/td><\/tr><\/tbody><\/table><\/div>\n <\/div>\n <div class=\"phork-meta\">\n  <a href=\"https:\/\/p.cweiske.de\/664\/rev-raw\/bdff9ef98bd11c0fe2cc642793d808e3a8fa4ca4\/1-add.sql\" style=\"float: right\">view raw source<\/a>\n  <a href=\"https:\/\/p.cweiske.de\/664#1-add.sql\">1-add.sql<\/a>\n <\/div>\n<\/div>\n    <div class=\"phork-file\">\n <div class=\"phork-content\">\n  <style type=\"text\/css\">\/**\n * GeSHi (C) 2004 - 2007 Nigel McNie, 2007 - 2014 Benny Baumann\n * (http:\/\/qbnz.com\/highlighter\/ and http:\/\/geshi.org\/)\n *\/\n.sql .de1, .sql .de2 {font: normal normal 1em\/1.2em monospace; margin:0; padding:0; background:none; vertical-align:top;}\n.sql  {font-family:monospace;}\n.sql .imp {font-weight: bold; color: red;}\n.sql li, .sql .li1 {color: #DDD;}\n.sql .ln {width:1px;text-align:right;margin:0;padding:0 2px;vertical-align:top;}\n.sql .kw1 {color: #993333; font-weight: bold;}\n.sql .co1 {color: #808080; font-style: italic;}\n.sql .coMULTI {color: #808080; font-style: italic;}\n.sql .es0 {color: #000099; font-weight: bold;}\n.sql .br0 {color: #66cc66;}\n.sql .sy0 {color: #66cc66;}\n.sql .st0 {color: #ff0000;}\n.sql .nu0 {color: #cc66cc;}\n.sql span.xtra { display:block; }\n<\/style><div class=\"code\"><table class=\"sql\"><tbody><tr class=\"li1\"><td class=\"ln\"><pre class=\"de1\">1\n2\n3\n4\n5\n6\n7\n8\n9\n10\n11\n12\n13\n14\n15\n16\n17\n18\n19\n20\n21\n<\/pre><\/td><td class=\"de1\"><pre class=\"de1\"><span class=\"kw1\">UPDATE<\/span> node_data\n<span class=\"kw1\">JOIN<\/span> nodes <span class=\"kw1\">ON<\/span> nodes<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> node_data<span class=\"sy0\">.<\/span>node_id\n<span class=\"kw1\">SET<\/span> external <span class=\"sy0\">=<\/span> JSON_REMOVE<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> JSON_UNQUOTE<span class=\"br0\">&#40;<\/span>JSON_SEARCH<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> <span class=\"st0\">'one'<\/span><span class=\"sy0\">,<\/span> <span class=\"st0\">'hpm-api'<\/span><span class=\"sy0\">,<\/span> <span class=\"kw1\">NULL<\/span><span class=\"sy0\">,<\/span> <span class=\"st0\">'$.export_portals'<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span>\n<span class=\"kw1\">WHERE<\/span>\n&#160; JSON_CONTAINS<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> <span class=\"st0\">'[&quot;hpm-api&quot;]'<\/span><span class=\"sy0\">,<\/span> <span class=\"st0\">'$.export_portals'<\/span><span class=\"br0\">&#41;<\/span>\n&#160; <span class=\"kw1\">AND<\/span> nodes<span class=\"sy0\">.<\/span>available_for_rent <span class=\"sy0\">=<\/span> <span class=\"nu0\">1<\/span>;\n&#160;\n<span class=\"kw1\">UPDATE<\/span> node_data\n<span class=\"kw1\">JOIN<\/span> nodes <span class=\"kw1\">ON<\/span> nodes<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> node_data<span class=\"sy0\">.<\/span>node_id\n<span class=\"kw1\">SET<\/span> external <span class=\"sy0\">=<\/span> JSON_REMOVE<span class=\"br0\">&#40;<\/span>external<span class=\"sy0\">,<\/span> <span class=\"st0\">'$.export_portals'<\/span><span class=\"br0\">&#41;<\/span>\n<span class=\"kw1\">WHERE<\/span>\n&#160; external<span class=\"sy0\">-&gt;<\/span><span class=\"st0\">'$.export_portals'<\/span> <span class=\"sy0\">=<\/span> JSON_ARRAY<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span>\n&#160; <span class=\"kw1\">AND<\/span> nodes<span class=\"sy0\">.<\/span>available_for_rent <span class=\"sy0\">=<\/span> <span class=\"nu0\">1<\/span>;\n&#160;\n<span class=\"kw1\">UPDATE<\/span> node_data\n<span class=\"kw1\">JOIN<\/span> nodes <span class=\"kw1\">ON<\/span> nodes<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> node_data<span class=\"sy0\">.<\/span>node_id\n<span class=\"kw1\">SET<\/span> external <span class=\"sy0\">=<\/span> <span class=\"kw1\">NULL<\/span>\n<span class=\"kw1\">WHERE<\/span>\n&#160; external <span class=\"sy0\">=<\/span> JSON_OBJECT<span class=\"br0\">&#40;<\/span><span class=\"br0\">&#41;<\/span>\n&#160; <span class=\"kw1\">AND<\/span> nodes<span class=\"sy0\">.<\/span>available_for_rent <span class=\"sy0\">=<\/span> <span class=\"nu0\">1<\/span>;\n&#160;<\/pre><\/td><\/tr><\/tbody><\/table><\/div>\n <\/div>\n <div class=\"phork-meta\">\n  <a href=\"https:\/\/p.cweiske.de\/664\/rev-raw\/bdff9ef98bd11c0fe2cc642793d808e3a8fa4ca4\/2-remove.sql\" style=\"float: right\">view raw source<\/a>\n  <a href=\"https:\/\/p.cweiske.de\/664#2-remove.sql\">2-remove.sql<\/a>\n <\/div>\n<\/div>\n<\/div>\n"}