Mastering Dynamic JSON Fields in MySQL: Real‑World Cases and Pitfalls

Background

In a multi‑tenant system that serves many business lines, common fields are stored in a shared schema while each line’s custom requirements are handled through extensible JSON columns. The JSON columns are stored using MySQL's JSON data type, and developers frequently need to manipulate them with MySQL JSON functions.

Common JSON Functions

The following MySQL JSON functions are frequently used: ->: Return value from a JSON column after evaluating a path (same as JSON_EXTRACT()). ->>: Return value and unquote the result (same as JSON_UNQUOTE(JSON_EXTRACT())). JSON_APPEND(): Append data to a JSON document. JSON_ARRAY(): Create a JSON array. JSON_ARRAY_APPEND(): Append data to a JSON array. JSON_ARRAY_INSERT(): Insert into a JSON array. JSON_CONTAINS(): Test whether a JSON document contains a specific object at a path. JSON_CONTAINS_PATH(): Test whether any data exists at a path. JSON_DEPTH(): Maximum depth of a JSON document. JSON_EXTRACT(): Return data from a JSON document. JSON_INSERT(): Insert data into a JSON document. JSON_KEYS(): Return an array of keys from a JSON document. JSON_LENGTH(): Number of elements in a JSON document. JSON_MERGE(): Merge JSON documents, preserving duplicate keys (deprecated). JSON_MERGE_PATCH(): Merge JSON documents, replacing duplicate keys. JSON_MERGE_PRESERVE(): Merge JSON documents, preserving duplicate keys. JSON_OBJECT(): Create a JSON object. JSON_PRETTY(): Pretty‑print a JSON document. JSON_QUOTE(): Quote a JSON document. JSON_REMOVE(): Remove data from a JSON document. JSON_REPLACE(): Replace values in a JSON document. JSON_SEARCH(): Find a path to a value within a JSON document. JSON_SET(): Insert or update data in a JSON document. JSON_STORAGE_SIZE(): Storage size of the binary representation. JSON_TYPE(): Type of a JSON value. JSON_UNQUOTE(): Unquote a JSON value. JSON_VALID(): Validate a JSON value.

Typical Use Cases

Dynamic Extension Query

Many business lines store optional attributes in a JSON column called extendMap. Example JSON data:

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

This JSON is used as a filter condition for inventory queries during outbound order creation. In the service layer the dynamic attributes are mapped to a Map that corresponds to extendMap.

Weight Management Deep Dive

Only the “Huaguan” line supports weight inventory; other lines do not. Weight data is stored in a JSON column extend_content with the path $.stockInfo.stockWeight. Example:

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

Updating weight can be done with a single‑row CASE WHEN THEN statement:

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

During batch updates, if a detail’s stockWeight is NULL, the corresponding WHEN THEN clause is omitted, causing the whole extend_content to be set to NULL. This leads to unexpected nullification of the JSON column.

Layered Debugging

Three debugging layers were applied:

First layer : Discovered that JSON_SET fails when the target JSON column is NULL. Added an initialization step that converts NULL to an empty JSON object ( {}) using COALESCE and JSON_OBJECT().

Second layer : Encountered a syntax error when the generated SQL contained ELSE extend_content without a preceding WHEN. The erroneous fragment looked like extend_content = case ELSE extend_content end,. The fix was to replace the ELSE clause with a harmless WHEN 1=1 THEN extend_content construct.

Third layer : Realized that when **all** details have stockWeight = NULL, both the WHEN THEN and the corrected WHEN 1=1 clauses are omitted, leaving the CASE expression syntactically incomplete. The final solution was to always emit a valid WHEN clause, e.g., CASE WHEN 1=1 THEN extend_content END, ensuring the statement is parsable regardless of input.

MyBatis dynamic SQL snippets used in the implementation:

<trim prefix="extend_content=case" suffix="end,">
  <foreach collection="list" item="item" index="index" close="ELSE extend_content">
    <if test="item.stockWeight != null and item.stockWeight != 0">
      WHEN <include refid="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>

After applying the three‑layer fixes, batch updates work correctly for all three scenarios: all details have weight, none have weight, and a mix of both.

Conclusion

The article demonstrates how to safely manipulate JSON columns in MySQL, highlights subtle bugs caused by NULL JSON values during dynamic batch updates, and provides concrete MySQL and MyBatis patterns to guarantee robust SQL generation.