项目场景:
需对json内容中name进行排序
问题描述
SELECT JSON_EXTRACT(c_director_leader,'$[*].name')
FROM t_prop_unit tpu WHERE (tpu.c_del = 0 AND tpu.c_border_unit = 0)
ORDER BY JSON_EXTRACT(c_director_leader,'$[*].name') desc LIMIT 30
原因分析:
因为原来数据类型是 List
解决方案:
指定 list的下标,直接拿内容
SELECT JSON_EXTRACT(c_director_leader,‘ [ 0 ] . n a m e ′ ) F R O M t p r o p u n i t t p u W H E R E ( t p u . c d e l = 0 A N D t p u . c b o r d e r u n i t = 0 ) O R D E R B Y J S O N E X T R A C T ( c d i r e c t o r l e a d e r , ′ [0].name') FROM t_prop_unit tpu WHERE (tpu.c_del = 0 AND tpu.c_border_unit = 0) ORDER BY JSON_EXTRACT(c_director_leader,' [0].name′)FROMtpropunittpuWHERE(tpu.cdel=0ANDtpu.cborderunit=0)ORDERBYJSONEXTRACT(cdirectorleader,′[0].name’) DESC LIMIT 30