QQ咨询
电话
微信扫码咨询
JSON字段在不规则数据结构中有重要作用，典型的如自定义字段、自定义表单等。
达梦8的JSON数据以字符串方式存储，即varchar2，最大长度为8000字节；或clob，最大长度为100G-1字节。
一般建议加上IS JSON约束进行检查：
c2 varchar2(100) CONSTRAINT cs_json_c2 CHECK (c2 IS JSON(STRICT)) -- 严格模式
c3 varchar2(100) CONSTRAINT cs_json_c3 CHECK (c3 IS JSON(LAX)) -- 宽松模式（默认模式）
oracle 12cR2 版本开始相对较完整的支持json，使用字符串或二进制方式方式存储，即varchar2，最大长度为4000字节；或clob blob。oracle建议使用blob，但为了方便一般还是用clob。
一般建议加上IS JSON约束进行检查：
c2 varchar2(100) CONSTRAINT cs_json_c2 CHECK (c2 IS JSON(STRICT)) -- 严格模式
c3 varchar2(100) CONSTRAINT cs_json_c3 CHECK (c3 IS JSON(LAX)) -- 宽松模式（默认模式）
oracle 21c 版本支持原生json类型字段，且对json操作的支持更加全面完整。
postgresql对json的支持相对较早，postgresql 13版本功能就很完整了。支持原生json jsonb字段类型。jsonb有完整的功能，一般必须使用jsonb。
mysql5.7.22版本对json支持相对完整。支持原生json字段。mysql8.0的json性能上有较大提升。MySQL对json的支持较其它数据库更弱。
通过key获取json里的value，是使用json字段最直接方式。获取到的value，可以进行比较、排序等处理。
假设有表test，字段json_，json_数据为
{"a":"abc","b":123,"d":true,"e":1111885200,"f":[1,2,3,4],"g":["1","2","3","4"]}
# 达梦、Oracle
select json_value(t.json_,'$.a') from test t
# Postgresql
select t.json_ ->> 'a' from test t
# MySQL
select t.json_ ->> '$.a' from test t
获取到的value数据，不管在json里是什么数据类型，一律转换为字符串类型。
json里通常有4种级别数据类型，string number boolean date，其中date用number或字符串代替。另外还有数组类型。
这就涉及到数据库json字段中最复杂的一个问题：json里的数据类型和sql的数据类型如何转换、比较和排序。
最简单的方式就像上节一样，一律转成sql的字符串。但这在比较和排序上会出问题。
达梦和Oracle要求显式的转换成SQL的数据类型，即获取json的value数据时，必须指定转换成哪种sql类型。如不指定，则默认转成varchar（字符串）。
如json里是boolean类型，转varchar则为true false，转nubmer则为1 0。
# 转数值类型
json_value(t.json_,'$.a' returning number)
# 转字符串类型
json_value(t.json_,'$.a' returning varchar)
# 不指定，则默认转字符串类型
json_value(t.json_,'$.a')
postgresql则有独立的json内部数据类型，且与SQL数据类型不可进行比较等操作。比如json里的字符串和SQL的字符串是两种不同的数据类型，不可以进行比较操作。
获取json数据且转换成SQL字符串类型，使用->>操作符；获取json原始数据，使用->操作符。
# 转换成SQL字符串类型
select t.json_ ->> 'a' from test t
# 可以和字符串进行比较
select t.json_ ->> 'a' from test t where t.json_ ->> 'a' = 'abc'
# 获取json数据类型
select t.json_ -> 'a' from test t
# json字符串不可以和sql字符串进行比较，报错
select t.json_ -> 'a' from test t where t.json_ -> 'a' = 'abc'
json数据类型要和SQL数据类型进行比较，则需要进行数据类型转换。将json数据类型转为SQL数据类型，或者将SQL数据类型转为json数据类型。
可以使用to_jsonb将SQL数据类型转换成json类型。
select t.json_ -> 'a' from test t where t.json_ -> 'a' = to_jsonb(?)
select t.json_ -> 'a' from test t where t.json_ -> 'a' = '"abc"'::jsonb
json数据类型可以直接进行排序，无需转换类型。
select t.json_ -> 'a' from test t order by t.json_ -> 'a'
MySQL也有独立的json内部数据类型，但可以自动转换成对于的SQL数据类型。所以json的数据类型可以和SQL的数据类型进行比较。
获取json数据且转换成SQL字符串类型，使用->>操作符；获取json原始数据，使用->操作符。
select t.json_ -> '$.a' from test t where t.json_ -> '$.a' = 'abc'
json数据类型可以直接进行排序，无需转换类型。
select t.json_ -> '$.a' from test t order by t.json_ -> '$.a'
数据判断是json查询的难点。
需要将json的Array数据转成表。
select * from test t where exists(
select * from jsonb_array_elements_text(json_query(t.json_,'$.f' WITH CONDITIONAL WRAPPER)) t_sub
where t_sub.value = '1'
)
可以将json的Array数据转成表。
select * from test t where exists(
select * from json_table(json_query(t.json_,'$.f' WITH CONDITIONAL WRAPPER), '$[*]' COLUMNS (value VARCHAR2(1000) PATH '$')) t_sub
where t_sub.value = '1'
)
其它更高级的判断方式：
SELECT t.json_ FROM test t WHERE json_exists(t.json_, '$.f[*] ? (@ == $v1)' PASSING 3 AS "v1");
可以判断Array中是否包含某个元素，但数据类型必须匹配。
select t.json_ -> 'f' from test t order by t.json_ -> 'f' @> to_jsonb(?)
其它更高级的判断方式：
jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}')
jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', json_build_object('min', 2, max, 4))
可以判断Array中是否包含某个元素，但数据类型必须匹配。
可以使用JSON_QUOTE函数将SQL数据类型转为json数据类型。
select t.json_ -> '$.f' from test t order by t.json_ -> '$.f' @> JSON_QUOTE(?)
判断是否包含任意元素：
JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]")
可以将数据表里的一列作为key，另一列作为value，构建json数据。
# 达梦
select jsonb_object_agg(t.name_,t.value_) from test t
# Oracle
select JSON_OBJECTAGG(KEY t.name_ VALUE t.value_) from test t
# PostgreSQL
select jsonb_object_agg(t.name_,t.value_) from test t
# MySQL
select JSON_OBJECTAGG(t.name_, t.value_) from test t