抽丝剥茧探穷境！一次数据库JSON字段的深度使用实践
京东物流 郭忠强约 9 分钟背景
在我们系统中，承接多种行业，多种商家的，多个业务条线，不同场景的的不同业务诉求，在实现上将个性和通用字段隔离，通用字段是所有条线通用逻辑所共用的标准字段，对于个别条线的个性化诉求，则通过个性化扩展字段来实现。
通用字段，作用于通用逻辑，所有条线走到相应功能时，会对通用字段读写。而对于个性扩展字段而言，只有用到个性化功能时，才会对个性化扩展字段进行赋值。在数据库持久化存储上，用不到该个性化扩展字段时，该字段无需存储，不占用额外存储空间。
在表结构上，使用JSON类型字段进行存储。在日常扩展字段的开发过程中，不可避免的用到JSON函数进行字段处理。
常用JSON函数一览
JSON字段实现项目场景诉求典型案例分析
JSON字段对我们系统来说并不陌生，有不少场景使用，下面有一些典型的使用场景案例先放出来作为JSON字段项目使用背景铺垫一下。
扩展字段的存储和查询
动态扩展查询案例
在二手库或逆向条件，有比较多的扩展属性，其他条线却没有这些属性值，这些属性以JSON扩展的方式存储。
下面是一个JSON扩展字段的JSON数据示例:
{"ppCode": "PPDA4302865239B10F","zoneNo": "STAGE-OUTBOUND","zoneName": "出库暂存区","zoneType": "t","extendMap": {"cid1": 13765,"cid2": 14192,"cid3": 14533,"deptId": "1","jdFlag": 1,"symbol": 300,"brandId": 52368,"cbjPrice": 2034,"salesPin": "xumingchen","commonType": 0,"wareSource": 100,"orderSource": "it100","supplierCode": "lgde","outTransferId": "1284009718","inboundSourceType": 100,"purchaseChannelId": "1001"},"storeCode": "","sourceModule": "wms-pick","isolationZone": false,"stageZoneType": "OUTBOUND"}
这些扩展字段支持作为查询条件过滤库存，用于出库建单。
在服务层面为了支持动态增减的属性条件，条件字段定义为Map类型，与 JSON 字段中的 extendMap 所对应。
元素包含条件使用案例
设备组条件
数值管理深度剖析案例
案例背景
在现有的场景中，目前仅华冠场景支持重量库存，其他条线暂无库存管理。在本案例中，重量库存通过个性化扩展字段实现，JSON类型。
JSON类型的字段名是 extend_content，其中重量字段 stockWeight 的 JSON path是 $.stockInfo.stockWeight 。
stockInfo 的内容示例如下：
{"businessNo": "OT2008735812539129856","stockWeight": 630,"businessType": "WMS_PICK","businessTypeName": "出库拣货"}
增减重量库存
单行更新
通过CASE WHEN THEN 方式批量更新JSON字段中的重量库存剥茧第一层
在调试中，发现JSON字段，如果想要通过 json_set 赋值，必须依赖于该字段不能为null，如果json_set 该JSON字段值为null，则更新不上，不会产生SQL语法错误。
对于存量的历史null值字段统计，并做一次初始化。
初始化历史数据
统计JSON字段extend_content值为null 的数据
将 extend_content.stockInfo 为 null 的数据进行初始化
以上SQL，通过JSON_OBJECT函数将null值的JSON字段，初始化为一个空JSON对象，肉眼看上去是 {}。
剥茧第二层
除了上面历史null值的JSON字段外，还发现一个更为复杂的场景，JSON字段更新前不为null，但是本次赋值更新操作却导致JSON字段整体变成了null，匪夷所思！
单行更新方式因为是一次是更新一行，没有问题。
批量更新，如果所有的明细stockWeight字段都有值，更新也没问题。如果一次批量更新的明细中，有的stockWeight字段有值，有的没有值，则更新会有问题，不会出现SQL语法错误，但是整个extend_content JSON字段会被错误地更新为null。
{"requestHeader": {"sourceModule": "inventory","requestIp": "11.50.45.137","warehouseNo": "6_6_618","businessType": "INV-CHANGE-PROFIT-LOSS","businessTypeName": "盘盈亏","businessNo": "CP2009231067168407553","uuid": "wms.inv.change.profit.6_6_618.CP2009231067168407553.2009231067554283520.12","operator": "guozhongqiang5"},"increaseStockDetailList": [{"detailBusinessNo": "DPPT20092299369586442242","stockLocationIndicator": {"locationNo": "01","containerLevel1": "","containerLevel2": ""},"stockSkuIndicator": {"sku": "EMG172002001","lotNo": "-1","skuLevel": "100","packCode": "8c59689e8972a14e4883b0ea755b3702","ownerNo": "EBU4398046536982"},"increaseOperateType": "normalIncrease","stockQty": 1.0000,"recommendLocationNo": "","externalNo": "CP2009231067168407553","uniqueStockList": [],"reason": {}},{"detailBusinessNo": "DPPT2009229936958644224","stockLocationIndicator": {"locationNo": "01","containerLevel1": "","containerLevel2": ""},"stockSkuIndicator": {"sku": "EMG172002003","lotNo": "-1","skuLevel": "100","packCode": "8c59689e8972a14e4883b0ea755b3702","ownerNo": "EBU4398046536982"},"increaseOperateType": "normalIncrease","stockQty": 0.0000,"stockWeight": 3000,"recommendLocationNo": "","externalNo": "CP2009231067168407553","uniqueStockList": [],"reason": {}}]}
我们可以看到，上面这个入参有两个明细，对应的SKU分别是EMG172002001 和 EMG172002003。第一个明细只有stockQty字段无stockWeight字段，第二个同时有stockQty字段，和stockWeight字段，stockWeight值为 3000。
此时，批量更新形成的SQL如下：
UPDATEst_stocksetstock_qty =caseWHEN deleted = 0AND warehouse_no = '6_6_618'AND id = 2008872347100020736AND status = 0THEN stock_qty + 1.0000WHEN deleted = 0AND warehouse_no = '6_6_618'AND id = 2008872388812374016AND status = 0THEN stock_qty + 0.0000end,extend_content =caseWHEN deleted = 0AND warehouse_no = '6_6_618'AND id = 2008872388812374016AND status = 0THEN JSON_SET(COALESCE(extend_content, '{}'), '$.stockInfo', COALESCE(JSON_EXTRACT(extend_content, '$.stockInfo'), JSON_OBJECT()))ELSE extend_contentend,extend_content =caseWHEN deleted = 0AND warehouse_no = '6_6_618'AND id = 2008872388812374016AND status = 0THEN json_set(extend_content, '$.stockInfo.stockWeight', IFNULL(extend_content ->> '$.stockInfo.stockWeight', 0) + IFNULL(3000.0, 0))end,update_time = now(),version = version + 1,update_user = 'guozhongqiang5',extend_content = json_set(extend_content, '$.stockInfo.businessType', 'INV-CHANGE-PROFIT-LOSS', '$.stockInfo.businessTypeName', '盘盈亏', '$.stockInfo.businessNo', 'CP2009231067168407553333')WHERE(deleted = 0AND warehouse_no = '6_6_618'AND id = 2008872347100020736AND status = 0)or(deleted = 0AND warehouse_no = '6_6_618'AND id = 2008872388812374016AND status = 0)
问题出现下图圈红的部分：在上面的场景中，一次批量更新有两个明细，其中一个因为stockWeight为null，不会被动态拼接到SQL中，这个无对应的 WHEN THEN 则 extend_content 被置为null，另一个明细因为有 WHEN THEN 则赋值正常。
此时直观的想法是，既然因为stockWeight为null的明细走不上 WHEN THEN ，何不通过 ELSE 赋值为extend_content 本身来解决呢。按此思路进行代码调整如下：
调试明细stockWeigh不为空，验证通过。
剥茧第三层
但是，当所有明细stockWeight为null的时候，因为 ELSE extend_content 在 if条件满足的时候才会动态拼接，当，此时 WHEN THEN 和 ELSE都不会拼接进去，语法不通过。
那么，把 ELSE extend_content 从 if 条件判断拿出来，放在 end的前面总可以了吧。
这里借助 foreach 的 close 来拼接 ELSE extend_content 语句。
在测试环境验证时，当更新入参至少一个明细中的 stockWeight 不为null时，确实没问题。当所有明细的stockWeight都为null时，新的问题来了，报错信息及分析过程如下：
### The error occurred while setting parameters### SQL: UPDATE st_stock set stock_qty=case WHEN deleted = 0 AND warehouse_no = ? AND id = ? and stock_qty >= ? * -1 THEN stock_qty + ? WHEN deleted = 0 AND warehouse_no = ? AND id = ? and stock_qty >= ? * -1 THEN stock_qty + ? end, diff_qty=case WHEN deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 THEN diff_qty + ? WHEN deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 THEN diff_qty + ? end, extend_content=case ELSE extend_content end, extend_content=case ELSE extend_content end, update_time = now() , version = version + 1 , update_user = ? , extend_content = json_set(extend_content, ?, ?, ?, ?, ?, ?) WHERE ( deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 AND status = 0 ) or ( deleted = 0 AND warehouse_no = ? AND id = ? and diff_qty >= ? * -1 AND status = 0 ) /* [SQLMarking] statementId: com.jdwl.wms.stock.infrastructure.jdbc.main.dao.StockOperationDao.simpleDecreaseDiffQty */### Cause: com.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near 'ELSE extend_content end,extend_content=case ELSE extend_content' at line 36; bad SQL grammar []; nested exception is com.sql.jdbc.exceptions.jdbc4.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near 'ELSE extend_content end,
{"requestHeader": {"sourceModule": "inventory","requestIp": "11.158.12.73","warehouseNo": "6_6_618","businessType": "INV-PROFIT-LOSS-TRANSFER-STOCK-MATCH","businessTypeName": "盘盈、盘亏单预占转移","businessNo": "CL2009892868717576192","uuid": "wms.inv.change.lock.transfer.loss.6_6_618.2009892868977623040.0","operator": "guozhongqiang5"},"stockLockTransformType": "difference2Change","stockLockTransformDetailList": [{"srcBusinessNo": "DPPT2009892261415911424","destBusinessNo": "CL2009892868717576192","stockSkuIndicator": {"sku": "EMG172002002","lotNo": "-1","skuLevel": "100","packCode": "8c59689e8972a14e4883b0ea755b3702","ownerNo": "EBU4398046536982"},"stockLocationIndicator": {"locationNo": "01","containerLevel1": "","containerLevel2": ""},"qty": 2},{"srcBusinessNo": "DPPT2009892261415911424","destBusinessNo": "CL2009892868717576192","stockSkuIndicator": {"sku": "EMG172002004","lotNo": "-1","skuLevel": "100","packCode": "8c59689e8972a14e4883b0ea755b3702","ownerNo": "EBU4398046536982"},"stockLocationIndicator": {"locationNo": "01","containerLevel1": "","containerLevel2": ""},"qty": 4}],"serialLockTransformDetailList": []}
入参本身没什么问题，是符合要求的入参，从本次需求的特征上来看只是没有重量字段，重量字段非必填，也不是必须的，在很多场景下也不涉及重量库存的变化。
数据
数据库中的待更新数据本身也没有问题。
到这里，已经确认入参是正常场景，待更新数据也没问题，是SQL本身存在问题。
<trimprefix="extend_content=case"suffix="end,"><foreachcollection="list"item="item"index="index"close="ELSE extend_content"><iftest="item.stockWeight != null and item.stockWeight != 0">WHEN<includerefid="simpleCommonIncreaseClauseOfItem"/>THEN json_set(extend_content, #{item.stockWeightJsonPath, jdbcType=VARCHAR}, IFNULL(extend_content ->> #{item.stockWeightJsonPath, jdbcType=VARCHAR}, 0) + IFNULL(#{item.stockWeight, jdbcType=DECIMAL}, 0))</if></foreach></trim>
extend_content=case ELSE extend_content end,显然，这个语法不通过的。
解决方法也比较简单，就是把 ELSE 语句改成 WHEN THEN 语句，具体如下：
extend_content=case WHEN 1=1 THEN extend_content end,至此，批量更新所有明细全有stockWeight，全无stockWeight，部分有部分无stockWeight的场景，均已支持完毕。
再来一层
回过头看，还有另外一个思路处理更为简单，不用 CASE WHEN THEN 方式新写一个批量更新方法：
推荐阅读