DM8 SQL语言中JSON函数实战：高效处理半结构化数据

一、JSON函数概述与应用场景

1.1 JSON数据格式简介

JSON(JavaScript Object Notation)是一种轻量级的数据交换格式,采用完全独立于语言的文本格式,易于人阅读和编写,同时也易于机器解析和生成。JSON基于JavaScript的一个子集,是理想的半结构化数据存储格式。

#publish-mermaid-1786238291865-0{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238291865-0 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238291865-0 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238291865-0 .error-icon{fill:#552222;}#publish-mermaid-1786238291865-0 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238291865-0 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238291865-0 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238291865-0 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238291865-0 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238291865-0 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238291865-0 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238291865-0 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238291865-0 .marker.cross{stroke:#333333;}#publish-mermaid-1786238291865-0 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238291865-0 p{margin:0;}#publish-mermaid-1786238291865-0 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238291865-0 .cluster-label text{fill:#333;}#publish-mermaid-1786238291865-0 .cluster-label span{color:#333;}#publish-mermaid-1786238291865-0 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238291865-0 .label text,#publish-mermaid-1786238291865-0 span{fill:#333;color:#333;}#publish-mermaid-1786238291865-0 .node rect,#publish-mermaid-1786238291865-0 .node circle,#publish-mermaid-1786238291865-0 .node ellipse,#publish-mermaid-1786238291865-0 .node polygon,#publish-mermaid-1786238291865-0 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238291865-0 .rough-node .label text,#publish-mermaid-1786238291865-0 .node .label text,#publish-mermaid-1786238291865-0 .image-shape .label,#publish-mermaid-1786238291865-0 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238291865-0 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238291865-0 .rough-node .label,#publish-mermaid-1786238291865-0 .node .label,#publish-mermaid-1786238291865-0 .image-shape .label,#publish-mermaid-1786238291865-0 .icon-shape .label{text-align:center;}#publish-mermaid-1786238291865-0 .node.clickable{cursor:pointer;}#publish-mermaid-1786238291865-0 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238291865-0 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238291865-0 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238291865-0 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238291865-0 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238291865-0 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238291865-0 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238291865-0 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238291865-0 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238291865-0 .cluster text{fill:#333;}#publish-mermaid-1786238291865-0 .cluster span{color:#333;}#publish-mermaid-1786238291865-0 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238291865-0 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238291865-0 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238291865-0 .icon-shape,#publish-mermaid-1786238291865-0 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238291865-0 .icon-shape p,#publish-mermaid-1786238291865-0 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238291865-0 .icon-shape .label rect,#publish-mermaid-1786238291865-0 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238291865-0 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238291865-0 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238291865-0 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].node rect,#publish-mermaid-1786238291865-0 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238291865-0 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291865-0 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291865-0 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

JSON数据结构

对象 Object

数组 Array

基础值 Value

{key: value, …}

[value, value, …]

string

number

boolean

null

object

array

1.2 DM8对JSON的支持能力

达梦数据库DM8提供了对JSON数据的全面支持,主要包括以下能力:

JSON数据的存储与管理

JSON路径表达式查询(JSONPath)

丰富的JSON处理函数

JSON数据与关系数据的相互转换

JSON索引优化

#publish-mermaid-1786238291989-1{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238291989-1 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238291989-1 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238291989-1 .error-icon{fill:#552222;}#publish-mermaid-1786238291989-1 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238291989-1 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238291989-1 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238291989-1 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238291989-1 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238291989-1 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238291989-1 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238291989-1 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238291989-1 .marker.cross{stroke:#333333;}#publish-mermaid-1786238291989-1 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238291989-1 p{margin:0;}#publish-mermaid-1786238291989-1 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238291989-1 .cluster-label text{fill:#333;}#publish-mermaid-1786238291989-1 .cluster-label span{color:#333;}#publish-mermaid-1786238291989-1 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238291989-1 .label text,#publish-mermaid-1786238291989-1 span{fill:#333;color:#333;}#publish-mermaid-1786238291989-1 .node rect,#publish-mermaid-1786238291989-1 .node circle,#publish-mermaid-1786238291989-1 .node ellipse,#publish-mermaid-1786238291989-1 .node polygon,#publish-mermaid-1786238291989-1 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238291989-1 .rough-node .label text,#publish-mermaid-1786238291989-1 .node .label text,#publish-mermaid-1786238291989-1 .image-shape .label,#publish-mermaid-1786238291989-1 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238291989-1 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238291989-1 .rough-node .label,#publish-mermaid-1786238291989-1 .node .label,#publish-mermaid-1786238291989-1 .image-shape .label,#publish-mermaid-1786238291989-1 .icon-shape .label{text-align:center;}#publish-mermaid-1786238291989-1 .node.clickable{cursor:pointer;}#publish-mermaid-1786238291989-1 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238291989-1 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238291989-1 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238291989-1 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238291989-1 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238291989-1 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238291989-1 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238291989-1 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238291989-1 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238291989-1 .cluster text{fill:#333;}#publish-mermaid-1786238291989-1 .cluster span{color:#333;}#publish-mermaid-1786238291989-1 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238291989-1 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238291989-1 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238291989-1 .icon-shape,#publish-mermaid-1786238291989-1 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238291989-1 .icon-shape p,#publish-mermaid-1786238291989-1 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238291989-1 .icon-shape .label rect,#publish-mermaid-1786238291989-1 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238291989-1 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238291989-1 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238291989-1 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].node rect,#publish-mermaid-1786238291989-1 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238291989-1 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291989-1 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238291989-1 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

DM8 JSON支持

存储能力

查询能力

构建能力

转换能力

优化能力

CLOB/VARCHAR存储

JSON类型校验

JSON_PATH路径查询

条件过滤

JSON_OBJECT构建对象

JSON_ARRAY构建数组

JSON_TABLE转关系表

关系数据转JSON

函数索引

查询优化

1.3 JSON函数应用场景分析

JSON函数在实际业务中有广泛的应用场景,主要包括:

配置数据管理:存储应用配置信息

日志数据处理:解析和查询日志JSON字段

API数据交互:处理来自外部接口的JSON数据

灵活字段扩展:使用JSON字段扩展表结构

半结构化数据存储:存储格式不统一的数据

#publish-mermaid-1786238292127-2{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238292127-2 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292127-2 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292127-2 .error-icon{fill:#552222;}#publish-mermaid-1786238292127-2 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238292127-2 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238292127-2 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238292127-2 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238292127-2 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238292127-2 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238292127-2 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238292127-2 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238292127-2 .marker.cross{stroke:#333333;}#publish-mermaid-1786238292127-2 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238292127-2 p{margin:0;}#publish-mermaid-1786238292127-2 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238292127-2 .cluster-label text{fill:#333;}#publish-mermaid-1786238292127-2 .cluster-label span{color:#333;}#publish-mermaid-1786238292127-2 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238292127-2 .label text,#publish-mermaid-1786238292127-2 span{fill:#333;color:#333;}#publish-mermaid-1786238292127-2 .node rect,#publish-mermaid-1786238292127-2 .node circle,#publish-mermaid-1786238292127-2 .node ellipse,#publish-mermaid-1786238292127-2 .node polygon,#publish-mermaid-1786238292127-2 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292127-2 .rough-node .label text,#publish-mermaid-1786238292127-2 .node .label text,#publish-mermaid-1786238292127-2 .image-shape .label,#publish-mermaid-1786238292127-2 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238292127-2 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238292127-2 .rough-node .label,#publish-mermaid-1786238292127-2 .node .label,#publish-mermaid-1786238292127-2 .image-shape .label,#publish-mermaid-1786238292127-2 .icon-shape .label{text-align:center;}#publish-mermaid-1786238292127-2 .node.clickable{cursor:pointer;}#publish-mermaid-1786238292127-2 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238292127-2 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238292127-2 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238292127-2 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238292127-2 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292127-2 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292127-2 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292127-2 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238292127-2 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238292127-2 .cluster text{fill:#333;}#publish-mermaid-1786238292127-2 .cluster span{color:#333;}#publish-mermaid-1786238292127-2 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238292127-2 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238292127-2 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238292127-2 .icon-shape,#publish-mermaid-1786238292127-2 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292127-2 .icon-shape p,#publish-mermaid-1786238292127-2 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238292127-2 .icon-shape .label rect,#publish-mermaid-1786238292127-2 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292127-2 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238292127-2 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238292127-2 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].node rect,#publish-mermaid-1786238292127-2 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238292127-2 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292127-2 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292127-2 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}结构固定半结构化不常查询

业务需求

数据特点

关系型字段

JSON字段

JSON存储

使用JSON函数处理

查询: JSON_VALUE/QUERY

构建: JSON_OBJECT/ARRAY

转换: JSON_TABLE

二、核心JSON函数详解

2.1 JSON_VALUE函数使用

JSON_VALUE函数用于从JSON字符串中提取标量值(字符串、数字、布尔值等),返回单个标量值。

语法格式:

JSON_VALUE(json_string, json_path)

参数说明:

json_string:JSON格式的字符串

json_path:JSON路径表达式,指定要提取的值的位置

示例:

— 创建测试表
CREATE TABLE json_test (
id INT,
info CLOB
);
— 插入测试数据
INSERT INTO json_test VALUES (1, '{"name": "zhangsan", "age": 30, "city": "beijing"}');
INSERT INTO json_test VALUES (2, '{"name": "lisi", "age": 25, "city": "shanghai"}');
— 使用JSON_VALUE提取值
SELECT id, JSON_VALUE(info, '$.name') AS name,
JSON_VALUE(info, '$.age') AS age,
JSON_VALUE(info, '$.city') AS city
FROM json_test;

JSON路径表达式说明:

| 路径表达式 | 含义 | 示例 |

| — | — | — |

| $ | 根对象 | $ |

| $.key | 对象属性 | $.name |

| $.key.subkey | 嵌套属性 | $.address.city |

| $.key[index] | 数组元素 | $.hobbies[0] |

| $.key[] | 所有数组元素 | $.hobbies[] |

2.2 JSON_QUERY函数使用

JSON_QUERY函数用于从JSON字符串中提取对象或数组片段,返回JSON格式的字符串。

语法格式:

JSON_QUERY(json_string, json_path)

示例:

— 插入包含数组和嵌套对象的数据
INSERT INTO json_test VALUES (3, '{
"name": "wangwu",
"age": 28,
"hobbies": ["reading", "swimming", "coding"],
"address": {
"city": "guangzhou",
"street": "zhongshan road"
}
}');
— 提取数组
SELECT JSON_QUERY(info, '$.hobbies') AS hobbies
FROM json_test WHERE id = 3;
— 提取嵌套对象
SELECT JSON_QUERY(info, '$.address') AS address
FROM json_test WHERE id = 3;
— 提取数组中特定元素
SELECT JSON_QUERY(info, '$.hobbies[0]') AS first_hobby
FROM json_test WHERE id = 3;

2.3 JSON_EXISTS函数使用

JSON_EXISTS函数用于判断JSON字符串中是否存在指定路径的值,返回布尔值TRUE或FALSE。

语法格式:

JSON_EXISTS(json_string, json_path)

示例:

— 判断属性是否存在
SELECT id, JSON_EXISTS(info, '$.address') AS has_address
FROM json_test;
— 在WHERE条件中使用
SELECT id, JSON_VALUE(info, '$.name') AS name
FROM json_test
WHERE JSON_EXISTS(info, '$.address.city');
— 判断数组是否存在
SELECT id
FROM json_test
WHERE JSON_EXISTS(info, '$.hobbies');

2.4 JSON_OBJECT函数使用

JSON_OBJECT函数用于构建JSON对象,将键值对转换为JSON格式字符串。

语法格式:

JSON_OBJECT(key1 VALUE value1, key2 VALUE value2, …)

示例:

— 构建简单JSON对象
SELECT JSON_OBJECT('name' VALUE 'zhaoliu', 'age' VALUE 35) AS json_obj;
— 从表数据构建JSON
SELECT JSON_OBJECT(
'id' VALUE id,
'name' VALUE JSON_VALUE(info, '$.name'),
'age' VALUE JSON_VALUE(info, '$.age')
) AS json_obj
FROM json_test;
— 嵌套JSON对象
SELECT JSON_OBJECT(
'user' VALUE JSON_OBJECT(
'name' VALUE 'qianqi',
'age' VALUE 40
),
'role' VALUE 'admin'
) AS nested_json;

2.5 JSON_ARRAY函数使用

JSON_ARRAY函数用于构建JSON数组,将多个值组合成JSON数组格式字符串。

语法格式:

JSON_ARRAY(value1, value2, …)

示例:

— 构建简单数组
SELECT JSON_ARRAY('apple', 'banana', 'cherry') AS fruits;
— 构建数字数组
SELECT JSON_ARRAY(1, 2, 3, 4, 5) AS numbers;
— 构建混合类型数组
SELECT JSON_ARRAY('test', 123, TRUE, NULL) AS mixed;
— 聚合构建数组
SELECT JSON_ARRAYAGG(JSON_VALUE(info, '$.name')) AS all_names
FROM json_test;

2.6 JSON_TABLE函数使用

JSON_TABLE函数是DM8中功能最强大的JSON处理函数,它可以将JSON数据转换为关系表格式,便于进行SQL查询和分析。

语法格式:

JSON_TABLE(json_string, json_path COLUMNS (
column_name column_type PATH json_path,
column_name column_type PATH json_path,
…
))

示例:

— 创建包含JSON数组的测试数据
CREATE TABLE json_array_test (
id INT,
data CLOB
);
INSERT INTO json_array_test VALUES (1, '{
"department": "tech",
"employees": [
{"name": "zhangsan", "age": 30, "position": "engineer"},
{"name": "lisi", "age": 25, "position": "tester"},
{"name": "wangwu", "age": 28, "position": "pm"}
]
}');
— 使用JSON_TABLE展开数组
SELECT t.id,
jt.name,
jt.age,
jt.position
FROM json_array_test t,
JSON_TABLE(t.data, '$.employees[*]' COLUMNS (
name VARCHAR(50) PATH '$.name',
age INT PATH '$.age',
position VARCHAR(50) PATH '$.position'
)) jt;

三、JSON函数实战案例

3.1 JSON数据查询与提取

本节通过实际案例演示如何使用JSON函数进行数据查询和提取。

案例一:电商订单数据查询

— 创建订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_info CLOB,
create_time TIMESTAMP
);
— 插入订单数据
INSERT INTO orders VALUES (1, 1001, '{
"product": "laptop",
"price": 5999,
"quantity": 1,
"attributes": {
"brand": "lenovo",
"model": "ThinkPad",
"color": "black"
},
"tags": ["electronics", "office", "premium"]
}', SYSDATE);
INSERT INTO orders VALUES (2, 1002, '{
"product": "phone",
"price": 3999,
"quantity": 2,
"attributes": {
"brand": "huawei",
"model": "Mate60",
"color": "white"
},
"tags": ["electronics", "mobile"]
}', SYSDATE);
— 查询订单基本信息
SELECT order_id,
customer_id,
JSON_VALUE(order_info, '$.product') AS product,
JSON_VALUE(order_info, '$.price') AS price,
JSON_VALUE(order_info, '$.quantity') AS quantity
FROM orders;
— 查询订单属性
SELECT order_id,
JSON_VALUE(order_info, '$.attributes.brand') AS brand,
JSON_VALUE(order_info, '$.attributes.model') AS model,
JSON_VALUE(order_info, '$.attributes.color') AS color
FROM orders;
— 计算订单总价
SELECT order_id,
JSON_VALUE(order_info, '$.product') AS product,
CAST(JSON_VALUE(order_info, '$.price') AS DECIMAL(10,2)) *
CAST(JSON_VALUE(order_info, '$.quantity') AS INT) AS total_price
FROM orders;
— 查询包含特定标签的订单
SELECT order_id, JSON_QUERY(order_info, '$.tags') AS tags
FROM orders
WHERE JSON_EXISTS(order_info, '$.tags');

案例二:日志数据分析

— 创建日志表
CREATE TABLE app_logs (
log_id INT PRIMARY KEY,
log_time TIMESTAMP,
log_data CLOB
);
— 插入日志数据
INSERT INTO app_logs VALUES (1, SYSDATE, '{
"level": "ERROR",
"module": "auth",
"message": "login failed",
"details": {
"user": "admin",
"ip": "192.168.1.100",
"attempt": 3
}
}');
INSERT INTO app_logs VALUES (2, SYSDATE, '{
"level": "INFO",
"module": "order",
"message": "order created",
"details": {
"order_id": "ORD20240101001",
"amount": 999
}
}');
— 按日志级别统计
SELECT JSON_VALUE(log_data, '$.level') AS log_level,
COUNT(*) AS count
FROM app_logs
GROUP BY JSON_VALUE(log_data, '$.level');
— 查询错误日志
SELECT log_id,
log_time,
JSON_VALUE(log_data, '$.module') AS module,
JSON_VALUE(log_data, '$.message') AS message,
JSON_QUERY(log_data, '$.details') AS details
FROM app_logs
WHERE JSON_VALUE(log_data, '$.level') = 'ERROR';

3.2 JSON数据构建与修改

本节演示如何使用JSON函数构建和修改JSON数据。

案例一:动态构建JSON配置

— 创建配置表
CREATE TABLE app_config (
config_id INT PRIMARY KEY,
config_name VARCHAR(100),
config_data CLOB,
update_time TIMESTAMP
);
— 使用JSON_OBJECT构建配置
INSERT INTO app_config VALUES (
1,
'database_config',
JSON_OBJECT(
'host' VALUE 'localhost',
'port' VALUE 5236,
'database' VALUE 'DM8',
'username' VALUE 'SYSDBA',
'pool_size' VALUE 10,
'timeout' VALUE 30,
'ssl' VALUE FALSE
),
SYSDATE
);
— 构建嵌套配置
INSERT INTO app_config VALUES (
2,
'app_config',
JSON_OBJECT(
'app' VALUE JSON_OBJECT(
'name' VALUE 'MyApp',
'version' VALUE '1.0.0'
),
'features' VALUE JSON_ARRAY(
'authentication',
'logging',
'monitoring'
),
'limits' VALUE JSON_OBJECT(
'max_users' VALUE 1000,
'max_requests' VALUE 10000
)
),
SYSDATE
);
— 从关系数据构建JSON
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20)
);
INSERT INTO users VALUES (1, 'zhangsan', 'zhangsan@example.com', '13800138000');
INSERT INTO users VALUES (2, 'lisi', 'lisi@example.com', '13900139000');
— 将用户数据转为JSON格式
SELECT JSON_OBJECT(
'user_id' VALUE user_id,
'username' VALUE username,
'contact' VALUE JSON_OBJECT(
'email' VALUE email,
'phone' VALUE phone
)
) AS user_json
FROM users;

案例二:JSON数据更新

— 创建用户扩展信息表
CREATE TABLE user_profile (
user_id INT PRIMARY KEY,
profile CLOB
);
INSERT INTO user_profile VALUES (1, '{"nickname": "zs", "bio": "developer", "skills": ["Java", "Python"]}');
— 更新JSON中的特定字段
UPDATE user_profile
SET profile = JSON_OBJECT(
'nickname' VALUE 'zsf',
'bio' VALUE JSON_VALUE(profile, '$.bio'),
'skills' VALUE JSON_QUERY(profile, '$.skills')
)
WHERE user_id = 1;
— 添加新字段
UPDATE user_profile
SET profile = JSON_OBJECT(
'nickname' VALUE JSON_VALUE(profile, '$.nickname'),
'bio' VALUE JSON_VALUE(profile, '$.bio'),
'skills' VALUE JSON_QUERY(profile, '$.skills'),
'avatar' VALUE '/images/avatar1.png',
'level' VALUE 5
)
WHERE user_id = 1;

3.3 JSON与关系数据转换

本节演示JSON数据与关系数据之间的相互转换。

案例一:JSON数组展开为关系表

— 创建包含JSON数组的商品表
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
variants CLOB
);
INSERT INTO products VALUES (1, 'T-Shirt', '[
{"size": "S", "color": "white", "price": 99, "stock": 100},
{"size": "M", "color": "white", "price": 99, "stock": 150},
{"size": "L", "color": "black", "price": 109, "stock": 80},
{"size": "XL", "color": "black", "price": 119, "stock": 50}
]');
— 使用JSON_TABLE展开变体信息
SELECT p.product_id,
p.product_name,
v.size,
v.color,
v.price,
v.stock
FROM products p,
JSON_TABLE(p.variants, '$[*]' COLUMNS (
size VARCHAR(10) PATH '$.size',
color VARCHAR(20) PATH '$.color',
price DECIMAL(10,2) PATH '$.price',
stock INT PATH '$.stock'
)) v;
— 条件过滤
SELECT p.product_name,
v.size,
v.color,
v.price
FROM products p,
JSON_TABLE(p.variants, '$[*]' COLUMNS (
size VARCHAR(10) PATH '$.size',
color VARCHAR(20) PATH '$.color',
price DECIMAL(10,2) PATH '$.price',
stock INT PATH '$.stock'
)) v
WHERE v.stock > 100 AND v.price < 100;

案例二:关系数据聚合为JSON

— 创建部门员工表
CREATE TABLE dept_employee (
dept_id INT,
dept_name VARCHAR(50),
emp_id INT,
emp_name VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO dept_employee VALUES (1, 'tech', 101, 'zhangsan', 15000);
INSERT INTO dept_employee VALUES (1, 'tech', 102, 'lisi', 12000);
INSERT INTO dept_employee VALUES (1, 'tech', 103, 'wangwu', 18000);
INSERT INTO dept_employee VALUES (2, 'market', 201, 'zhaoliu', 10000);
INSERT INTO dept_employee VALUES (2, 'market', 202, 'qianqi', 11000);
— 按部门聚合员工信息为JSON
SELECT dept_id,
dept_name,
JSON_OBJECT(
'dept_id' VALUE dept_id,
'dept_name' VALUE dept_name,
'employees' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'emp_id' VALUE emp_id,
'emp_name' VALUE emp_name,
'salary' VALUE salary
)
)
) AS dept_json
FROM dept_employee
GROUP BY dept_id, dept_name;

四、JSON函数性能优化建议

4.1 索引策略优化

对于频繁查询的JSON字段,建议建立函数索引以提高查询性能。

— 为JSON字段创建函数索引
CREATE INDEX idx_json_name ON json_test(JSON_VALUE(info, '$.name'));
CREATE INDEX idx_json_age ON json_test(CAST(JSON_VALUE(info, '$.age') AS INT));
— 复合函数索引
CREATE INDEX idx_json_name_age ON json_test(
JSON_VALUE(info, '$.name'),
CAST(JSON_VALUE(info, '$.age') AS INT)
);
— 验证索引使用
EXPLAIN SELECT * FROM json_test
WHERE JSON_VALUE(info, '$.name') = 'zhangsan';

索引优化流程:

#publish-mermaid-1786238292356-3{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238292356-3 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292356-3 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292356-3 .error-icon{fill:#552222;}#publish-mermaid-1786238292356-3 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238292356-3 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238292356-3 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238292356-3 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238292356-3 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238292356-3 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238292356-3 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238292356-3 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238292356-3 .marker.cross{stroke:#333333;}#publish-mermaid-1786238292356-3 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238292356-3 p{margin:0;}#publish-mermaid-1786238292356-3 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238292356-3 .cluster-label text{fill:#333;}#publish-mermaid-1786238292356-3 .cluster-label span{color:#333;}#publish-mermaid-1786238292356-3 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238292356-3 .label text,#publish-mermaid-1786238292356-3 span{fill:#333;color:#333;}#publish-mermaid-1786238292356-3 .node rect,#publish-mermaid-1786238292356-3 .node circle,#publish-mermaid-1786238292356-3 .node ellipse,#publish-mermaid-1786238292356-3 .node polygon,#publish-mermaid-1786238292356-3 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292356-3 .rough-node .label text,#publish-mermaid-1786238292356-3 .node .label text,#publish-mermaid-1786238292356-3 .image-shape .label,#publish-mermaid-1786238292356-3 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238292356-3 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238292356-3 .rough-node .label,#publish-mermaid-1786238292356-3 .node .label,#publish-mermaid-1786238292356-3 .image-shape .label,#publish-mermaid-1786238292356-3 .icon-shape .label{text-align:center;}#publish-mermaid-1786238292356-3 .node.clickable{cursor:pointer;}#publish-mermaid-1786238292356-3 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238292356-3 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238292356-3 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238292356-3 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238292356-3 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292356-3 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292356-3 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292356-3 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238292356-3 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238292356-3 .cluster text{fill:#333;}#publish-mermaid-1786238292356-3 .cluster span{color:#333;}#publish-mermaid-1786238292356-3 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238292356-3 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238292356-3 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238292356-3 .icon-shape,#publish-mermaid-1786238292356-3 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292356-3 .icon-shape p,#publish-mermaid-1786238292356-3 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238292356-3 .icon-shape .label rect,#publish-mermaid-1786238292356-3 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292356-3 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238292356-3 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238292356-3 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].node rect,#publish-mermaid-1786238292356-3 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238292356-3 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292356-3 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292356-3 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}高频查询低频查询是否

JSON查询需求

查询频率

创建函数索引

全表扫描

分析查询条件

单字段查询

多字段查询

单字段函数索引

复合函数索引

验证执行计划

是否走索引

优化完成

调整索引策略

4.2 查询优化技巧

避免重复解析:对同一JSON字段多次调用JSON_VALUE时,考虑使用子查询或CTE

合理使用CAST:JSON_VALUE返回字符串,需要时及时转换为合适类型

限制返回数据:使用JSON_QUERY提取片段而非整个JSON

善用JSON_TABLE:处理数组数据时,JSON_TABLE比多次JSON_VALUE更高效

— 优化前:重复解析JSON
SELECT JSON_VALUE(info, '$.name') AS name,
JSON_VALUE(info, '$.age') AS age,
JSON_VALUE(info, '$.city') AS city,
JSON_VALUE(info, '$.name') || '@example.com' AS email
FROM json_test;
— 优化后:使用CTE减少重复解析
WITH parsed_json AS (
SELECT id,
JSON_VALUE(info, '$.name') AS name,
JSON_VALUE(info, '$.age') AS age,
JSON_VALUE(info, '$.city') AS city
FROM json_test
)
SELECT name, age, city, name || '@example.com' AS email
FROM parsed_json;
— 使用JSON_TABLE一次性提取多个字段
SELECT jt.name, jt.age, jt.city
FROM json_test t,
JSON_TABLE(t.info, '$' COLUMNS (
name VARCHAR(50) PATH '$.name',
age INT PATH '$.age',
city VARCHAR(50) PATH '$.city'
)) jt;

查询优化决策流程:

#publish-mermaid-1786238292489-4{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238292489-4 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292489-4 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292489-4 .error-icon{fill:#552222;}#publish-mermaid-1786238292489-4 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238292489-4 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238292489-4 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238292489-4 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238292489-4 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238292489-4 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238292489-4 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238292489-4 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238292489-4 .marker.cross{stroke:#333333;}#publish-mermaid-1786238292489-4 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238292489-4 p{margin:0;}#publish-mermaid-1786238292489-4 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238292489-4 .cluster-label text{fill:#333;}#publish-mermaid-1786238292489-4 .cluster-label span{color:#333;}#publish-mermaid-1786238292489-4 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238292489-4 .label text,#publish-mermaid-1786238292489-4 span{fill:#333;color:#333;}#publish-mermaid-1786238292489-4 .node rect,#publish-mermaid-1786238292489-4 .node circle,#publish-mermaid-1786238292489-4 .node ellipse,#publish-mermaid-1786238292489-4 .node polygon,#publish-mermaid-1786238292489-4 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292489-4 .rough-node .label text,#publish-mermaid-1786238292489-4 .node .label text,#publish-mermaid-1786238292489-4 .image-shape .label,#publish-mermaid-1786238292489-4 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238292489-4 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238292489-4 .rough-node .label,#publish-mermaid-1786238292489-4 .node .label,#publish-mermaid-1786238292489-4 .image-shape .label,#publish-mermaid-1786238292489-4 .icon-shape .label{text-align:center;}#publish-mermaid-1786238292489-4 .node.clickable{cursor:pointer;}#publish-mermaid-1786238292489-4 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238292489-4 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238292489-4 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238292489-4 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238292489-4 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292489-4 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292489-4 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292489-4 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238292489-4 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238292489-4 .cluster text{fill:#333;}#publish-mermaid-1786238292489-4 .cluster span{color:#333;}#publish-mermaid-1786238292489-4 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238292489-4 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238292489-4 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238292489-4 .icon-shape,#publish-mermaid-1786238292489-4 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292489-4 .icon-shape p,#publish-mermaid-1786238292489-4 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238292489-4 .icon-shape .label rect,#publish-mermaid-1786238292489-4 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292489-4 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238292489-4 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238292489-4 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].node rect,#publish-mermaid-1786238292489-4 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238292489-4 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292489-4 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292489-4 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}单值提取片段提取数组展开条件判断是否是否

JSON查询

查询类型

JSON_VALUE

JSON_QUERY

JSON_TABLE

JSON_EXISTS

多次提取同一JSON

使用CTE/子查询

直接使用

需要类型转换

在COLUMNS中指定类型

使用默认VARCHAR

优化完成

4.3 常见问题与解决方案

问题一:JSON_VALUE返回NULL

— 原因:路径不存在或值不是标量
— 解决方案:使用JSON_EXISTS先判断,或使用默认值
SELECT id,
COALESCE(JSON_VALUE(info, '$.phone'), 'N/A') AS phone
FROM json_test;

问题二:数字比较不正确

— 原因:JSON_VALUE返回字符串,需要CAST转换
— 错误写法:
SELECT * FROM json_test WHERE JSON_VALUE(info, '$.age') > 25;
— 正确写法:
SELECT * FROM json_test
WHERE CAST(JSON_VALUE(info, '$.age') AS INT) > 25;

问题三:JSON格式校验

— 使用IS JSON条件校验
SELECT id,
CASE WHEN info IS JSON THEN 'valid' ELSE 'invalid' END AS json_valid
FROM json_test;
— 过滤有效JSON数据
SELECT * FROM json_test WHERE info IS JSON;

问题排查流程:

#publish-mermaid-1786238292846-5{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238292846-5 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292846-5 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292846-5 .error-icon{fill:#552222;}#publish-mermaid-1786238292846-5 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238292846-5 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238292846-5 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238292846-5 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238292846-5 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238292846-5 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238292846-5 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238292846-5 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238292846-5 .marker.cross{stroke:#333333;}#publish-mermaid-1786238292846-5 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238292846-5 p{margin:0;}#publish-mermaid-1786238292846-5 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238292846-5 .cluster-label text{fill:#333;}#publish-mermaid-1786238292846-5 .cluster-label span{color:#333;}#publish-mermaid-1786238292846-5 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238292846-5 .label text,#publish-mermaid-1786238292846-5 span{fill:#333;color:#333;}#publish-mermaid-1786238292846-5 .node rect,#publish-mermaid-1786238292846-5 .node circle,#publish-mermaid-1786238292846-5 .node ellipse,#publish-mermaid-1786238292846-5 .node polygon,#publish-mermaid-1786238292846-5 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292846-5 .rough-node .label text,#publish-mermaid-1786238292846-5 .node .label text,#publish-mermaid-1786238292846-5 .image-shape .label,#publish-mermaid-1786238292846-5 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238292846-5 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238292846-5 .rough-node .label,#publish-mermaid-1786238292846-5 .node .label,#publish-mermaid-1786238292846-5 .image-shape .label,#publish-mermaid-1786238292846-5 .icon-shape .label{text-align:center;}#publish-mermaid-1786238292846-5 .node.clickable{cursor:pointer;}#publish-mermaid-1786238292846-5 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238292846-5 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238292846-5 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238292846-5 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238292846-5 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292846-5 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292846-5 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292846-5 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238292846-5 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238292846-5 .cluster text{fill:#333;}#publish-mermaid-1786238292846-5 .cluster span{color:#333;}#publish-mermaid-1786238292846-5 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238292846-5 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238292846-5 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238292846-5 .icon-shape,#publish-mermaid-1786238292846-5 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292846-5 .icon-shape p,#publish-mermaid-1786238292846-5 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238292846-5 .icon-shape .label rect,#publish-mermaid-1786238292846-5 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292846-5 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238292846-5 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238292846-5 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].node rect,#publish-mermaid-1786238292846-5 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238292846-5 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292846-5 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292846-5 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}返回NULL类型错误语法错误性能问题

JSON函数异常

异常类型

检查路径是否存在

检查是否需要CAST

检查JSON格式

检查索引使用

使用JSON_EXISTS判断

添加CAST转换

使用IS JSON校验

创建函数索引

问题解决

五、总结与最佳实践

5.1 使用场景总结

#publish-mermaid-1786238292972-6{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238292972-6 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292972-6 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238292972-6 .error-icon{fill:#552222;}#publish-mermaid-1786238292972-6 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238292972-6 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238292972-6 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238292972-6 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238292972-6 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238292972-6 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238292972-6 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238292972-6 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238292972-6 .marker.cross{stroke:#333333;}#publish-mermaid-1786238292972-6 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238292972-6 p{margin:0;}#publish-mermaid-1786238292972-6 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238292972-6 .cluster-label text{fill:#333;}#publish-mermaid-1786238292972-6 .cluster-label span{color:#333;}#publish-mermaid-1786238292972-6 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238292972-6 .label text,#publish-mermaid-1786238292972-6 span{fill:#333;color:#333;}#publish-mermaid-1786238292972-6 .node rect,#publish-mermaid-1786238292972-6 .node circle,#publish-mermaid-1786238292972-6 .node ellipse,#publish-mermaid-1786238292972-6 .node polygon,#publish-mermaid-1786238292972-6 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292972-6 .rough-node .label text,#publish-mermaid-1786238292972-6 .node .label text,#publish-mermaid-1786238292972-6 .image-shape .label,#publish-mermaid-1786238292972-6 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238292972-6 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238292972-6 .rough-node .label,#publish-mermaid-1786238292972-6 .node .label,#publish-mermaid-1786238292972-6 .image-shape .label,#publish-mermaid-1786238292972-6 .icon-shape .label{text-align:center;}#publish-mermaid-1786238292972-6 .node.clickable{cursor:pointer;}#publish-mermaid-1786238292972-6 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238292972-6 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238292972-6 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238292972-6 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238292972-6 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292972-6 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292972-6 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292972-6 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238292972-6 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238292972-6 .cluster text{fill:#333;}#publish-mermaid-1786238292972-6 .cluster span{color:#333;}#publish-mermaid-1786238292972-6 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238292972-6 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238292972-6 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238292972-6 .icon-shape,#publish-mermaid-1786238292972-6 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238292972-6 .icon-shape p,#publish-mermaid-1786238292972-6 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238292972-6 .icon-shape .label rect,#publish-mermaid-1786238292972-6 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238292972-6 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238292972-6 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238292972-6 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].node rect,#publish-mermaid-1786238292972-6 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238292972-6 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292972-6 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238292972-6 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

DM8 JSON函数应用

查询提取

数据构建

数据转换

性能优化

JSON_VALUE: 标量值提取

JSON_QUERY: 对象/数组提取

JSON_EXISTS: 路径存在判断

JSON_OBJECT: 对象构建

JSON_ARRAY: 数组构建

JSON_ARRAYAGG: 聚合为数组

JSON_TABLE: JSON转关系表

关系数据转JSON对象

函数索引优化

CTE减少重复解析

类型转换优化

5.2 最佳实践建议

合理设计JSON结构:避免过深的嵌套,保持结构清晰

规范命名:JSON键名使用统一命名规范(如小写加下划线)

索引优化:对高频查询字段建立函数索引

类型安全:使用CAST确保类型正确,避免隐式转换问题

错误处理:使用COALESCE或NULLIF处理可能的NULL值

性能监控:定期检查JSON查询的执行计划

5.3 注意事项说明

JSON_VALUE只能返回标量值,提取对象或数组请使用JSON_QUERY

JSON路径表达式区分大小写

JSON数组索引从0开始

大量JSON数据处理时,注意内存使用情况

JSON函数的结果可以作为其他SQL语句的输入

在事务中使用JSON函数时,注意锁的粒度

函数选择指南:

#publish-mermaid-1786238293100-7{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786238293100-7 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238293100-7 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786238293100-7 .error-icon{fill:#552222;}#publish-mermaid-1786238293100-7 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786238293100-7 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786238293100-7 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786238293100-7 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786238293100-7 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786238293100-7 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786238293100-7 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786238293100-7 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786238293100-7 .marker.cross{stroke:#333333;}#publish-mermaid-1786238293100-7 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786238293100-7 p{margin:0;}#publish-mermaid-1786238293100-7 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786238293100-7 .cluster-label text{fill:#333;}#publish-mermaid-1786238293100-7 .cluster-label span{color:#333;}#publish-mermaid-1786238293100-7 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786238293100-7 .label text,#publish-mermaid-1786238293100-7 span{fill:#333;color:#333;}#publish-mermaid-1786238293100-7 .node rect,#publish-mermaid-1786238293100-7 .node circle,#publish-mermaid-1786238293100-7 .node ellipse,#publish-mermaid-1786238293100-7 .node polygon,#publish-mermaid-1786238293100-7 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238293100-7 .rough-node .label text,#publish-mermaid-1786238293100-7 .node .label text,#publish-mermaid-1786238293100-7 .image-shape .label,#publish-mermaid-1786238293100-7 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786238293100-7 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786238293100-7 .rough-node .label,#publish-mermaid-1786238293100-7 .node .label,#publish-mermaid-1786238293100-7 .image-shape .label,#publish-mermaid-1786238293100-7 .icon-shape .label{text-align:center;}#publish-mermaid-1786238293100-7 .node.clickable{cursor:pointer;}#publish-mermaid-1786238293100-7 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786238293100-7 .arrowheadPath{fill:#333333;}#publish-mermaid-1786238293100-7 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786238293100-7 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786238293100-7 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238293100-7 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786238293100-7 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238293100-7 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786238293100-7 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786238293100-7 .cluster text{fill:#333;}#publish-mermaid-1786238293100-7 .cluster span{color:#333;}#publish-mermaid-1786238293100-7 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786238293100-7 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786238293100-7 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786238293100-7 .icon-shape,#publish-mermaid-1786238293100-7 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786238293100-7 .icon-shape p,#publish-mermaid-1786238293100-7 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786238293100-7 .icon-shape .label rect,#publish-mermaid-1786238293100-7 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786238293100-7 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786238293100-7 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786238293100-7 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].node rect,#publish-mermaid-1786238293100-7 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786238293100-7 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238293100-7 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786238293100-7 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}提取标量值提取对象/数组判断路径存在构建JSON对象构建JSON数组JSON转关系表聚合为JSON数组

JSON处理需求

需求类型

JSON_VALUE

JSON_QUERY

JSON_EXISTS

JSON_OBJECT

JSON_ARRAY

JSON_TABLE

JSON_ARRAYAGG

返回: 标量值

返回: JSON字符串

返回: BOOLEAN

返回: JSON对象字符串

返回: JSON数组字符串

返回: 关系表行

返回: JSON数组字符串

通过本文的详细介绍,相信读者已经对DM8数据库中的JSON函数有了全面的了解。在实际开发中,合理使用JSON函数可以大大简化半结构化数据的处理流程,提高开发效率。建议读者根据实际业务需求,选择合适的JSON函数,并遵循最佳实践进行优化。

DM8 SQL语言中JSON函数实战：高效处理半结构化数据