Deep Dive into Using JSON Fields in Databases: Practical Lessons and Pitfalls

Background and Motivation

In a multi‑tenant system we separate generic fields (shared by all business lines) from personalized extension fields that satisfy specific line requirements. Extension fields are stored in a JSON column so that they occupy no space when unused.

Common JSON Functions

Typical MySQL JSON functions such as JSON_SET, JSON_EXTRACT, and JSON_OBJECT are used to read and modify the stored data.

Typical Use Cases

Examples include dynamic filtering of inventory, weight‑stock management, and building queries based on varying attributes. A sample JSON payload is shown below:

{
  "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"
}

Weight Inventory Management Case

The extend_content column stores weight information under $.stockInfo.stockWeight. Sample data:

{
  "businessNo": "OT2008735812539129856",
  "stockWeight": 630,
  "businessType": "WMS_PICK",
  "businessTypeName": "出库拣货"
}

Updating weight uses a CASE WHEN THEN pattern:

UPDATE st_stock SET
  stock_qty = CASE WHEN ... THEN stock_qty + 1.0000 END,
  extend_content = CASE
    WHEN ... THEN JSON_SET(COALESCE(extend_content, '{}'), '$.stockInfo', COALESCE(JSON_EXTRACT(extend_content, '$.stockInfo'), JSON_OBJECT()))
    ELSE extend_content
  END,
  extend_content = CASE
    WHEN ... THEN 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 = 0 AND warehouse_no = '6_6_618' AND id = 2008872347100020736)
   OR (deleted = 0 AND warehouse_no = '6_6_618' AND id = 2008872388812374016);

Problem Discovery

During debugging we found that json_set fails when the target JSON column is NULL. Moreover, batch updates where some rows lack stockWeight cause the entire extend_content to become NULL because the generated WHEN THEN clause is omitted and the ELSE branch is not correctly placed.

First Fix – Adjusting ELSE Placement

Moving the ELSE extend_content clause outside the conditional block yields a syntactically valid statement: extend_content = CASE ELSE extend_content END, but this still produces an invalid CASE expression. The solution is to replace the stray ELSE with a harmless WHEN 1=1 THEN extend_content clause:

extend_content = CASE WHEN 1=1 THEN extend_content END,

Second Fix – Using WHEN THEN for All Paths

By converting the previous ELSE into a WHEN THEN clause, the batch update works whether all, none, or some rows contain stockWeight values.

Alternative Approach – Multi‑Statement Batch

Another option is to enable allowMultiQueries and execute separate statements for each condition, avoiding complex CASE logic altogether.

Conclusion

The investigation shows that careful handling of NULL JSON values and proper placement of CASE clauses are essential for reliable batch updates. The final pattern supports all three scenarios: all rows have weight, none have weight, and mixed cases.