就像我说的,在 MySQL 中解析 JSON 可能是一个挑战,因为您在这里处理基于文本的键。
所以你需要使用JSON_KEYS()
为了将它们与数字生成器结合使用,会生成动态 JSON 路径以供使用JSON_EXTRACT()
MySQL的8个函数JSON_TABLE()
让它变得容易得多..
请记住,这个答案纯粹是为了教育乐趣
比尔·卡尔文的评论
您应该以不同的方式构建 JSON 以支持
搜索你需要做的,否则你不应该使用 JSON。只需存储
多行数据,然后SELECT * FROM mytable WHERE is_filter = true
-
Query
SELECT
JSON_UNQUOTE(
JSON_EXTRACT(json , CONCAT('$.', SUBSTRING_INDEX(
SUBSTRING_INDEX(json_parsed, ',', number_generator.number)
, ','
, -1
), '.name'))) AS name
FROM (
SELECT
@row := @row + 1 AS number
FROM (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
SELECT @row := 0
) init_user_params
) AS number_generator
CROSS JOIN (
SELECT
SUBSTRING(json_keys, 2, json_keys_length - 2) AS json_parsed
, json_keys
, json
, JSON_LENGTH(json_keys) AS json_array_length
FROM (
SELECT
JSON_KEYS(record.json) AS json_keys
, json
, LENGTH(JSON_KEYS(record.json)) AS json_keys_length
FROM (
SELECT
'{
"Intitule": {
"name": "Intitule de la formation",
"stats": false,
"is_array": false,
"is_filter": true,
"chart": "pie",
"col": "6"
},
"Fin": {
"name": "Date de fin",
"stats": false,
"is_array": false,
"is_filter": false,
"chart": "pie",
"col": "6"
}
}' AS json
FROM
DUAL
) AS record
) AS json_information
) AS json_init
WHERE
number_generator.number BETWEEN 0 AND json_array_length
AND
JSON_EXTRACT(json , CONCAT('$.', SUBSTRING_INDEX(
SUBSTRING_INDEX(json_parsed, ',', number_generator.number)
, ','
, -1
), '.is_filter')) = true
Result
| name |
| ------------------------ |
| Intitule de la formation |
see demo