SQL进阶:如何把字段中的键值对转为JSON格式?

一、问题描述

假如某张表的某列是键值对数据,如何把这个键值对转为json格式,数据如下所示

dynasty	value
唐朝	唐太宗:李世民;唐高宗:李治;唐玄宗:李隆基;…
汉朝	汉高祖:刘邦;汉文帝:刘恒;汉景帝:刘启;汉武帝:‘刘彻’;…

需要转成下面这种格式

dynasty	value
唐朝	[{“key”:“唐太宗”,“value”:“李世民”,“order”:“1”},{“key”:“唐高宗”,“value”:“李治”,“order”:“2”},{“key”:“唐玄宗”,“value”:“李隆基”,“order”:“3”}…]
汉朝	[{“key”:“汉高祖”,“value”:“刘邦”,“order”:“1”},{“key”:“汉文帝”,“value”:“刘恒”,“order”:“2”},{“key”:“汉景帝”,“value”:“刘启”,“order”:“3”},{“key”:“汉武帝”,“value”:“刘彻”,“order”:“2”}…]

二、ORACLE

<一>、键值对拆分(REGEXP_SUBSTR)

with split_data as (
	select 
			dynasty,
	 		regexp_substr(value,'[^;]+',1,level) as part,
			level as part_num
	 from table
  connect by 
  	regexp_substr(value,'[^;]+',1,level) is not null
)
	select dynasty,
	       part_num,
	       regexp_substr(part,'[^;]+',1,1) as key,
	       regexp_substr(part,'[^;]+',1,2) as value
      from split_data

执行完就是如下形式

dynasty	part_num	key	value
唐朝	1	唐太宗	李世民
唐朝	2	唐高宗	李治
唐朝	3	唐玄宗	李隆基
汉朝	1	汉高祖	刘邦
汉朝	2	汉文帝	刘恒
汉朝	3	汉景帝	刘启
汉朝	4	汉武帝	刘彻
…

<二>、转为JSON

转为JSON有现成的转JSON函数,完整代码如下

with split_data as (
	select 
	        dynasty,
	 		regexp_substr(value,'[^;]+',1,level) as part,
			level as part_num
	 from table
  connect by 
  	regexp_substr(value,'[^;]+',1,level) is not null
),key_value_pairs as (
	select dynasty,
	       part_num,
	       regexp_substr(part,'[^;]+',1,1) as key,
	       regexp_substr(part,'[^;]+',1,2) as value
      from split_data
)
	select dynasty,
	       json_arrayagg(
				josn_object(
					'key' VALUE key,
					'value' VALUE value,
					'order' value split_num
					)
			)
	   from key_value_pairs
	  group by dynasty

<三>、不足

• 有的键值对比较大,在ORACLE是CLOB类型,不支持字符串拆分,只能用如下MYSQL方法,再进行数据同步

三、MYSQL

<一>、键值对拆分(RECURSIVE)

• MYSQL中没有正则字符串拆分函数,只能用递归进行拆分

with recursive kv_split as (
	select dynasty,
	       trim(substring_index(value,';',1)) as kv_pairs,
	       trim(substring(value,char_length(substring_index(value,';',1)) + 2)) as remaining,
	       1 as splt_num
	  from table
	  union all
	select dynasty,
	       trim(substring_index(remaining,';',1)) as kv_pairs,
	       trim(substring(remaining,char_length(substring_index(value,';',1)) + 2)) as remaining,
	       split_num + 1 as splt_num
	  from kv_split
	 where remaining != ''
)
	select dynasty,kv_pairs,splt_num from kv_split

执行完为如下形式,跟ORACLE大同小异

dynasty	part_num	kv_pairs
唐朝	1	唐太宗:李世民
唐朝	2	唐高宗:李治
唐朝	3	唐玄宗:李隆基
汉朝	1	汉高祖:刘邦
汉朝	2	汉文帝:刘恒
汉朝	3	汉景帝:刘启
汉朝	4	汉武帝:刘彻
…

可能会报如下错误;递归深度过大

• Recursive query aborted after 1001 iterations.Try increasing
  @@cte_max_recursion_depth to a larger value

解决方案:

• 查询SQL前加上如下代码

set @@cte_max_recursion_depth = 3000;
值可以随情况扩大

<二>、转为JSON

转成JSON跟ORACLE一样,有现成的转JSON函数,完整代码如下所示

with recursive kv_split as (
	select dynasty,
	       trim(substring_index(value,';',1)) as kv_pairs,
	       trim(substring(value,char_length(substring_index(value,';',1)) + 2)) as remaining,
	       1 as splt_num
	  from table
	  union all
	select dynasty,
	       trim(substring_index(remaining,';',1)) as kv_pairs,
	       trim(substring(remaining,char_length(substring_index(value,';',1)) + 2)) as remaining,
	       split_num + 1 as splt_num
	  from kv_split
	 where remaining != ''
)
	select dynasty,
	       json_arrayagg(
				json_object(
					'key',cast(substring_index(kv_pairs,':',1) as unsigned),
					'value',substring_index(kv_pairs,':',-1),
					'order',split_num
					)
			) as json_result
	  from kv_split
	 group by dynasty