<p>我就废话不多说了，大家还是直接看代码吧~</p>
<div class="blockcode">
<pre class="brush:sql;">
UPDATE tablename SET tags = jsonb_set(tags-'landuse_area', '{landuse_area}',('"' || round((ST_Area(ST_Transform(geom,4527)) * 0.0015) :: NUMERIC,3) || '"')::jsonb, TRUE) WHERE tags @> '{"name":"张三"}';</pre>
</div>
<p>round的目的是保留3位小数</p>
<p>st_area是计算多边形的面积，后面的0.0015是面积单位转换为亩</p>
<p>st_transform是投影转换，这里我表中原始的geom是4326，不适合计算面积，所以在这里转换为4527计算面积</p>
<p>::NUMERIC是转换为numeric数据类型</p>
<p>::jsonb是转换为jsonb类型</p>
<p><strong>补充：postgresql----JSON和JSONB类型的增删改查</strong></p>
<p>postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。</p>
<p>注意：键值对的键必须使用双引号</p>
<p>从PostgreSQL 9.3开始，json就成了postgres里的一种数据类型，也就是和varchar、int一样，我们表里的一个字段的类型可以为json了。</p>
<p>与此同时，postgres还提供了jsonb格式，jsonb格式是json的二进制形式，二者的区别在于json写入快，读取慢，jsonb写入慢，读取快，但在操作上，二者是没有区别的。下面以jsonb为例。</p>
<h3>创建表
</h3>
<p>假设我们要存储的json数据是这样的：</p>
<div class="blockcode">
<pre class="brush:sql;">
{
"id": ID
"name":"名字",
"age":年龄
}</pre>
</div>
<p>
建表语句如下：</p>
<div class="blockcode">
<pre class="brush:sql;">
create table if not exists name_age (
info jsonb
)
</pre>
</div>
<p>好了，这样就创建了一张表，里面只有一个 info 字段，下面开始进行CRUD操作。</p>
<h3>插入数据</h3>
<p>插入数据可以直接以json格式插入：</p>
<div class="blockcode">
<pre class="brush:sql;">
insert into name_age values('{"id":1,"name":"小明", "age":18}')</pre>
</div>
<p>在json里插入新的key值gender，如下：</p>
<div class="blockcode">
<pre class="brush:sql;">
SELECT info||'{"gender":"男"}'::jsonb from name_age where (info->>'id')::int4 = 1</pre>
</div>
<h3>查询数据</h3>
<p>Postgres里的查询需要用到查询符。比如说，我们要查询id为1的数据，语句如下：</p>
<div class="blockcode">
<pre class="brush:sql;">
select info from name_age where info @> '{"id":1}'::jsonb</pre>
</div>
<p>用到了 @> 这个查询符，表明info当前这条记录里的顶层json中有没有id为1的key-value对；有的话则满足条件。</p>
<p>再来一个复杂一点的查询的，查询 age>16 的记录，并且只显示 name ，语句如下：</p>
<div class="blockcode">
<pre class="brush:sql;">
select info->'name' from name_age where (info->>'age')::int4 > 16</pre>
</div>
<p>关于详细运算符使用，请参考官方文档： <a href="https://www.postgresql.org/docs/9.6/functions-json.html" rel="external nofollow" target="_blank">9.15. JSON Functions and Operators</a></p>
<h3>修改数据</h3>
<p>下面，将 age 从 18 改为 22 ，SQL语句：</p>
<div class="blockcode">
<pre class="brush:sql;">
SELECT info ||'{"age":22}'::jsonb from name_age where (info->>'id')::int4 = 1</pre>
</div>
<p>上述用法仅适用于9.5以上，9.5以下需要整个记录更新，不可以单独修改某个值。</p>
<p>
除了操作符以外，还可以使用函数操作： jsonb_set() ，函数签名如下：</p>
<div class="blockcode">
<pre class="brush:sql;">
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])</pre>
</div>
<p>详细使用可参考 <a href="https://www.postgresql.org/docs/9.6/functions-json.html" rel="external nofollow" target="_blank">9.15. JSON Functions and Operators</a></p>
<h3>删除数据</h3>
<p>删除age这个key，SQL如下：</p>
<div class="blockcode">
<pre class="brush:sql;">
SELECT info-'age' from name_age where (info->>'id')::int4 = 1</pre>
</div>
<p>直接用操作符 - 即可。</p>
<h3>总结
</h3>
<p>PostgreSQL 9.5以上的版本中有了很多方便的操作符，使得操作json变得非常方便了。</p>
<p>json和jsonb的操作符</p>
<table border="0">
<tbody>
<tr>
<td>操作符</td>
<td>右操作数类型</td>
<td>描述</td>
<td>示例</td>
<td>结果</td>
</tr>
<tr>
<td>-></td>
<td>int</td>
<td>获取JSON数组元素（索引从0开始）</td>
<td>select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;</td>
<td>{"c":"baz"}</td>
</tr>
<tr>
<td>-></td>
<td>text</td>
<td>通过键获取值</td>
<td>select '{"a": {"b":"foo"}}'::json->'a';</td>
<td>{"b":"foo"}</td>
</tr>
<tr>
<td>->> |
|
转播
