0x00 背景

最近解决了一个很有趣的问题，想在博客上分享一下，开始做crud工程师（笑）了以后，自然是渐渐地又熟悉起了sql。公司的项目正在使用postgresql，然后项目中有一个报表的数据模型，主要存储了各个时间段内来访人员的情况，其中有一项年龄分布的数据，因为年龄很多量大，需要用到jsonb的数据结构去存储。本文的故事就将会针对json的数据库操作展开讲讲。

0x01 问题

现在存在这样一个业务场景，很有可能同时有多个人与一个设备产生互动，然后设备将数据发送至服务器，有可能同时就会有多个数据来访数据产生。

原先统计报表时是这么做的，先用查询语句查询出目前的年龄分布json，然后在内存中计算完成后，用update语句去更新数据。看起来非常平常简单的操作，但是在并发多的情况下会出现问题。

比如两个routine几乎同时收到一个来访客人记录分别为20岁和22岁。未更新前，两个routine都会查到一个原来的值，例如{“20”:1, “21”:1, “22”:1}，表示20，21，22岁各有一人。

两个routine分别计算，

新来访客人为20岁的，就会在update语句中把更新值写成{“20”:2, “21”:1, “22”:1}

新来访客人为22岁的，就会在update语句中把更新值写成{“20”:1, “21”: 1, “22”: 2}

因为总会有先后顺序，最终存储的结果必然为两个结果中的一个，但上面的结果都不对，结果应该为{“20”:2, “21”:1, “22”:2}

所以，需要用一种方式直接摒弃前面的查询语句，直接在一句update语句中完成这件+1的事，这样才能保证数据递增操作的原子性。

0x02 json如何实现递增？

我们把问题分解一下，这是一个update语句，并且等号的两边一边是json数据的那一列，另一边是给某值+1。

所以两个步骤

1. 把json中某一年龄的值取出来
2. +1后塞回一个json

查询了一下postgregsql官方文档，发现分别有以下的函数或操作符能够完成以下操作。

->>操作符，可以做到取出这个json里面特定值的作用

jsonb_set函数能够改变一个json的值。

具体的用途可以查询文档得到，https://www.postgresql.org/docs/9.6/static/functions-json.html

于是我们就得到了如下语句（以来了一个23岁的用户为例）：

update report_events set age_count = jsonb_set(age_count, '{23}'， (to_char(to_number(age_count->>'23', '999') + 1, '999'))::jsonb, true)

因为->>取出来的是字符串类型的结果，所以需要to_number函数先转为数字，再+1，然后再想方设法转为jsonb类型，才能用jsonb_set赋值到原来的jsonb上。

0x03 case when语句的使用

上述的语句看起来非常不错，解决了我之前的问题，但还是有两个致命问题：

1. 当原数据中没有23这个key的时候会取到null，导致计算结果也为null，会造成整个json的数据被擦除。
2. 原数据本身为null的时候也不能用->>取值

想起来sql里面有一种case when语句的用法，其用途就是在不同情况下可以select或update不同的值，于是修改了上一步的操作。


update report_events set age_count = (
case when age_count is NULL then
'{"23": 1}'::jsonb
when age_count->>'23' is not NULL then
jsonb_set(age_count, '{23}', (to_char(to_number(age_count->>'23', '999') + 1, '999'))::jsonb, true)
else
jsonb_set(age_count, '{23}', '1'::jsonb, true)
end
);


上述语句就是进行了2种缺陷场景的考虑最终该写的句子，在测试环境中没有问题，改吧改吧上线了。

0x04 版本差异

我没有注意我看到的文档是9.5版本的，测试环境是一个比较新的版本，阿里云的生产环境却是9.4的版本，上面的sql语句造成了一些语法错误。

于是我对比了以下，我惊讶地发现9.4版本没有上述的jsonb_set函数，还好有帮助文档提供了解决方法。可以启用一个扩展。

create extension jsonbx;

不过似乎还是有其他问题，根据反复尝试，我发现->>操作符不能在case when后使用，于是我就又在找其他替代的方案，找到了?操作符，这个操作符就是判断这个key是不是存在于这个json的顶级key中。正是我们想要的，所以最终sql语句就变成了：
update report_events set age_count = (
case when age_count is NULL then '{"29": 1}'::jsonb
when age_count?'29' then jsonb_set(age_count, '{29}', (to_char(to_number(age_count->>'29', '999') + 1, '999'))::jsonb)
else jsonb_set(age_count, '{29}', '1'::jsonb) end );


 真是百转千折呀。