Fn_json_extract (function)
根据key值获取json字符串值
- Type: FUNCTION
-
Returns:
text - Deterministic: NO
- SQL data access: CONTAINS SQL
Parameters
| # | Mode | Name | Type |
|---|---|---|---|
| 1 | IN | p_jsonstr |
text |
| 2 | IN | p_key |
varchar(255) |
Body
Body is not pre-cached. To inspect: mysql --defaults-file=~/.my.cnf -e 'SHOW CREATE FUNCTIONFnjson_extract'._
Narrative
Business context: Hand-rolled JSON value extractor — predates xly's adoption of MySQL's native JSON_EXTRACT. Used heavily in cost/quotation/work-order calculation procs that store inline JSON snippets on detail rows (config parameters, dynamic field bags) and need to pull a single scalar out without strict JSON validity.
What it does: searches p_jsonstr for "<key>":"..." and returns the substring up to the next "; if that fails it falls back to "<key>":..., (unquoted value) and returns up to the next , or }. Pure regex/substring; tolerant of malformed JSON in a way JSON_EXTRACT is not.
Invocation: called from ~20 production procs including the calc family (Sp_Calc_sMpt, Sp_Calc_sScd, Sp_Calc_sSod, Sp_Calc_sWod), the manufacturing planning info procs (Sp_Manufacture_ProductionPlanInfo*), MES OEE state probes (Sp_Manufacture_GetAPSState*), Sp_Sis_GetMaxNo*, and the graphic report engine (Sp_Graphic_Report_Analysis_Data).