MySQL 获取 json 字段内的值 list数据

SELECT

CASE

WHEN JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_mesh"'))) = '1' THEN 'g'

WHEN JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_mesh"'))) = '2' THEN '%'

ELSE ''

END AS sieve_mesh_unit,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_mesh"'))) AS original_sieve_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_6_mesh"'))) AS sieve_6_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_12_mesh"'))) AS sieve_12_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_20_mesh"'))) AS sieve_20_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_30_mesh"'))) AS sieve_30_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_40_mesh"'))) AS sieve_40_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_50_mesh"'))) AS sieve_50_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_70_mesh"'))) AS sieve_70_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_100_mesh"'))) AS sieve_100_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_140_mesh"'))) AS sieve_140_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_200_mesh"'))) AS sieve_200_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."quality_sand_sampling_check_record@granularityAnalysis@sieve_270_mesh"'))) AS sieve_270_mesh,

JSON_UNQUOTE(JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']."jnpfId"'))) AS jnpf_id

FROM

fom_quality.quality_sand_sampling_check_record t

CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1) AS numbers

WHERE

t.granularityAnalysis IS NOT NULL

AND t.f_delete_mark IS NULL

AND t.room_number = {roomNumber}

AND JSON_EXTRACT(t.granularityAnalysis, CONCAT('$[', numbers.n, ']')) IS NOT NULL;

显示如下: