rich1.0phorkiehttps://p.cweiske.de/MySQL/MariaDB: Add value to arrayChristian Weiske86400900900<!-- embedding all files of https://p.cweiske.de/664 -->
<link rel="stylesheet" href="https://p.cweiske.de/css/embed.css"/>
<div class="phork" id="664">
<div class="phork-file">
<div class="phork-content">
<style type="text/css">/**
* GeSHi (C) 2004 - 2007 Nigel McNie, 2007 - 2014 Benny Baumann
* (http://qbnz.com/highlighter/ and http://geshi.org/)
*/
.sql .de1, .sql .de2 {font: normal normal 1em/1.2em monospace; margin:0; padding:0; background:none; vertical-align:top;}
.sql {font-family:monospace;}
.sql .imp {font-weight: bold; color: red;}
.sql li, .sql .li1 {color: #DDD;}
.sql .ln {width:1px;text-align:right;margin:0;padding:0 2px;vertical-align:top;}
.sql .kw1 {color: #993333; font-weight: bold;}
.sql .co1 {color: #808080; font-style: italic;}
.sql .coMULTI {color: #808080; font-style: italic;}
.sql .es0 {color: #000099; font-weight: bold;}
.sql .br0 {color: #66cc66;}
.sql .sy0 {color: #66cc66;}
.sql .st0 {color: #ff0000;}
.sql .nu0 {color: #cc66cc;}
.sql span.xtra { display:block; }
</style><div class="code"><table class="sql"><tbody><tr class="li1"><td class="ln"><pre class="de1">1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
</pre></td><td class="de1"><pre class="de1"><span class="co1">-- make "external" an JSON object if null</span>
<span class="kw1">UPDATE</span> node_data
<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
<span class="kw1">SET</span> external <span class="sy0">=</span> <span class="st0">"{}"</span>
<span class="kw1">WHERE</span>
  node_data<span class="sy0">.</span>external <span class="kw1">IS</span> <span class="kw1">NULL</span>
  <span class="kw1">AND</span> nodes<span class="sy0">.</span>available_for_rent <span class="sy0">=</span> <span class="nu0">1</span>;
 
<span class="co1">-- add "export_portals" array to "external" if it does not exist</span>
<span class="kw1">UPDATE</span> node_data
<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
<span class="kw1">SET</span> external <span class="sy0">=</span> JSON_SET<span class="br0">(</span>external<span class="sy0">,</span> <span class="st0">'$.export_portals'</span><span class="sy0">,</span> JSON_ARRAY<span class="br0">(</span><span class="br0">)</span><span class="br0">)</span>
<span class="kw1">WHERE</span>
  <span class="kw1">NOT</span> JSON_CONTAINS_PATH<span class="br0">(</span>external<span class="sy0">,</span> <span class="st0">'all'</span><span class="sy0">,</span> <span class="st0">'$.export_portals'</span><span class="br0">)</span>
  <span class="kw1">AND</span> nodes<span class="sy0">.</span>available_for_rent <span class="sy0">=</span> <span class="nu0">1</span>;
 
<span class="co1">-- add "hpm-api" value to external.export_portals</span>
<span class="kw1">UPDATE</span> node_data
<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
<span class="kw1">SET</span> external <span class="sy0">=</span> JSON_ARRAY_APPEND<span class="br0">(</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">)</span>
<span class="kw1">WHERE</span>
  <span class="kw1">NOT</span> JSON_CONTAINS<span class="br0">(</span>external<span class="sy0">,</span> <span class="st0">'["hpm-api"]'</span><span class="sy0">,</span> <span class="st0">'$.export_portals'</span><span class="br0">)</span>
  <span class="kw1">AND</span> nodes<span class="sy0">.</span>available_for_rent <span class="sy0">=</span> <span class="nu0">1</span>;
 </pre></td></tr></tbody></table></div>
</div>
<div class="phork-meta">
<a href="https://p.cweiske.de/664/rev-raw/bdff9ef98bd11c0fe2cc642793d808e3a8fa4ca4/1-add.sql" style="float: right">view raw source</a>
<a href="https://p.cweiske.de/664#1-add.sql">1-add.sql</a>
</div>
</div>
<div class="phork-file">
<div class="phork-content">
<style type="text/css">/**
* GeSHi (C) 2004 - 2007 Nigel McNie, 2007 - 2014 Benny Baumann
* (http://qbnz.com/highlighter/ and http://geshi.org/)
*/
.sql .de1, .sql .de2 {font: normal normal 1em/1.2em monospace; margin:0; padding:0; background:none; vertical-align:top;}
.sql {font-family:monospace;}
.sql .imp {font-weight: bold; color: red;}
.sql li, .sql .li1 {color: #DDD;}
.sql .ln {width:1px;text-align:right;margin:0;padding:0 2px;vertical-align:top;}
.sql .kw1 {color: #993333; font-weight: bold;}
.sql .co1 {color: #808080; font-style: italic;}
.sql .coMULTI {color: #808080; font-style: italic;}
.sql .es0 {color: #000099; font-weight: bold;}
.sql .br0 {color: #66cc66;}
.sql .sy0 {color: #66cc66;}
.sql .st0 {color: #ff0000;}
.sql .nu0 {color: #cc66cc;}
.sql span.xtra { display:block; }
</style><div class="code"><table class="sql"><tbody><tr class="li1"><td class="ln"><pre class="de1">1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
</pre></td><td class="de1"><pre class="de1"><span class="kw1">UPDATE</span> node_data
<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
<span class="kw1">SET</span> external <span class="sy0">=</span> JSON_REMOVE<span class="br0">(</span>external<span class="sy0">,</span> JSON_UNQUOTE<span class="br0">(</span>JSON_SEARCH<span class="br0">(</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">)</span><span class="br0">)</span><span class="br0">)</span>
<span class="kw1">WHERE</span>
  JSON_CONTAINS<span class="br0">(</span>external<span class="sy0">,</span> <span class="st0">'["hpm-api"]'</span><span class="sy0">,</span> <span class="st0">'$.export_portals'</span><span class="br0">)</span>
  <span class="kw1">AND</span> nodes<span class="sy0">.</span>available_for_rent <span class="sy0">=</span> <span class="nu0">1</span>;
 
<span class="kw1">UPDATE</span> node_data
<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
<span class="kw1">SET</span> external <span class="sy0">=</span> JSON_REMOVE<span class="br0">(</span>external<span class="sy0">,</span> <span class="st0">'$.export_portals'</span><span class="br0">)</span>
<span class="kw1">WHERE</span>
  external<span class="sy0">-></span><span class="st0">'$.export_portals'</span> <span class="sy0">=</span> JSON_ARRAY<span class="br0">(</span><span class="br0">)</span>
  <span class="kw1">AND</span> nodes<span class="sy0">.</span>available_for_rent <span class="sy0">=</span> <span class="nu0">1</span>;
 
<span class="kw1">UPDATE</span> node_data
<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
<span class="kw1">SET</span> external <span class="sy0">=</span> <span class="kw1">NULL</span>
<span class="kw1">WHERE</span>
  external <span class="sy0">=</span> JSON_OBJECT<span class="br0">(</span><span class="br0">)</span>
  <span class="kw1">AND</span> nodes<span class="sy0">.</span>available_for_rent <span class="sy0">=</span> <span class="nu0">1</span>;
 </pre></td></tr></tbody></table></div>
</div>
<div class="phork-meta">
<a href="https://p.cweiske.de/664/rev-raw/bdff9ef98bd11c0fe2cc642793d808e3a8fa4ca4/2-remove.sql" style="float: right">view raw source</a>
<a href="https://p.cweiske.de/664#2-remove.sql">2-remove.sql</a>
</div>
</div>
</div>