MYSQL JSON数组转置，将JSON数组中内容行转列

情况一：
主表结构：main_table

EMRID	JSON_FIELD
00001	[{“CODE”: “A01”, “DISPLAY_TYPE”: 1},{“CODE”: “A02”, “DISPLAY_TYPE”: 2}]
00002	[{“CODE”: “A02”, “DISPLAY_TYPE”: 3},{“CODE”: “A03”, “DISPLAY_TYPE”: 4}]
00003	{“CODE”: “A03”, “DISPLAY_TYPE”: 5}]

想要的结构

项目	CODE	DISPLAY_TYPE
00001	A01	1
00001	A02	2
00002	A02	3
00002	A03	4
00003	A03	5

SQL

SELECT
	EMRID,
	t.CODE,
	t.DISPLAY_TYPE
FROM
	main_table,
	json_table (
		JSON_FIELD,
		'$[*]' COLUMNS (
			CODE VARCHAR ( 10 ) path '$.CODE',
			DISPLAY_TYPEVARCHAR ( 10 ) path '$.DISPLAY_TYPE'
		)) t 
-- WHERE DISPLAY_TYPE= '2'  加条件查询

情况二：
主表结构：main_table

EMRID	JSON_FIELD
00001	[“a”,“b”,“c”]
00002	[“d”,“e”]
00003	[“f”]

想要的结构

项目	KEY
00001	a
00001	b
00001	c
00002	d
00002	e
00003	f

SQL

SELECT
	EMRID,
	t.v
FROM
	main_table,
	JSON_TABLE(KEY,"$[*]" COLUMNS ( v VARCHAR (128) PATH "$")) t

---

情况二实际应用:
查找title='张三’的json对象，获取其type值，需要先使用JSON_SEARCH获取title='张三’的路径，再将title替换为type。
由于JSON_SEARCH搜索的结果是个JSON_ARRAY(多个结果)，或者字符串(单个结果)，如：

select id,
JSON_SEARCH(JSON_EXTRACT(CONTENT, '$.cr[*].r' ), 'all', '张三', NULL, '$[*].title') ta
from table

查询结果：

EMRID	CONTENT	ta
00001	{json数据}	“$[1].title”
00002	{json数据}	[“$[2].title”, “$[13].title”]
00003	{json数据}	“$[1].title”

进行替换

-- JSON_UNQUOTE是将json_path中的双引号去掉，路径作为参数，不能带双引号
select 
	id,
	JSON_UNQUOTE(
		REPLACE(
			JSON_SEARCH(JSON_EXTRACT(CONTENT, '$.cr[*].r' ), 'all', '张三', NULL, '$[*].title'),'title','type'
		)
	) ta
from table

当JSON_SEARCH参数为ALL时，获取多个数据会自动存为JSON_ARRAY，单个数据为字符串，需要统一转化为JSON_ARRAY，并将title替换为type

select id,CONTENT,jt.v from 
	(select 
		id,CONTENT
		JSON_UNQUOTE(
			REPLACE(
				JSON_SEARCH(JSON_EXTRACT(CONTENT, '$.cr[*].r' ), 'all', '张三', NULL, '$[*].title'),'title','type'
			)
		) ta
	from table) t
-- IF(LEFT(ta,1)='[',ta,CONCAT('[',ta,']'))  是为了统一转为JSON_ARRAY
,JSON_TABLE(IF(LEFT(ta,1)='[',ta,CONCAT('[',ta,']')),"$[*]" COLUMNS ( v VARCHAR (128) PATH "$")) jt

查询结果(ID为00002的JSON_ARRAY被拆为两行)：

ID	CONTENT	v
00001	{json数据}	“$[1].type”
00002	{json数据}	“$[2].type”
00002	{json数据}	“$[13].type”
00003	{json数据}	“$[1].type”

接下来就可以进行查询了，获取id

select id from 
	(select 
		id,CONTENT
		JSON_UNQUOTE(
			REPLACE(
				JSON_SEARCH(JSON_EXTRACT(CONTENT, '$.cr[*].r' ), 'all', '张三', NULL, '$[*].title'),'title','type'
			)
		) ta
	from table) t
,JSON_TABLE(IF(LEFT(ta,1)='[',ta,CONCAT('[',ta,']')),"$[*]" COLUMNS ( v VARCHAR (128) PATH "$")) jt
where JSON_EXTRACT(t.CONTENT,jt.v)='表格'

具体json字段格式自由发挥吧，由于懒以及公司数据表保密，上述SQL手敲，未运行过，逻辑没问题