在MySQL 8中获取JSON内stations对象的父节点名称

假设你的表名为metro_data，存储JSON的字段为data，可以通过以下两种SQL方法实现需求：

方法1：先提取父节点键再验证路径 #

SELECT jt.parent_node
FROM metro_data md,
     JSON_TABLE(
         JSON_KEYS(md.data->>'$.hi'),
         '$[*]' COLUMNS (parent_node VARCHAR(255) PATH '$')
     ) jt
WHERE JSON_CONTAINS_PATH(md.data, 'one', CONCAT('$.hi.', jt.parent_node, '.stations')) = 1;

• JSON_KEYS(md.data->>'$.hi')：提取hi节点下的所有子节点键名，生成JSON数组
• JSON_TABLE(...)：将JSON数组转成关系型表，每行对应一个父节点键
• JSON_CONTAINS_PATH(...)：验证$.hi.父节点键.stations路径是否存在，'one'表示只要路径存在就返回1

方法2：直接解析节点并检查子键 #

SELECT jt.parent_node
FROM metro_data md,
     JSON_TABLE(
         md.data->>'$.hi',
         '$.*' COLUMNS (
             parent_node VARCHAR(255) PATH '$',
             node_data JSON PATH '$'
         )
     ) jt
WHERE JSON_CONTAINS_PATH(jt.node_data, 'one', '$.stations') = 1;

• JSON_TABLE(...)：把hi节点下的每个键值对拆成两行数据，分别存储父节点键和对应的JSON子节点
• JSON_CONTAINS_PATH(jt.node_data, 'one', '$.stations')：直接检查每个子节点是否包含stations键

测试初始化（可选） #

如果还没创建表，可先执行以下语句初始化数据：

CREATE TABLE metro_data (id INT AUTO_INCREMENT PRIMARY KEY, data JSON);
INSERT INTO metro_data (data) VALUES ('{"hi": {"blue_line_branch": {"stations": {"1_station_name": "यमुना बैंक", "2_station_name": "लक्समी नगर", "3_station_name": "निर्माण विहार", "4_station_name": "प्रीत विहार", "5_station_name": "कारकारडूमा", "5_notes_1": "", "5_notes_2": "", "6_station_name": "आनंद विहार िस्बत", "6_notes_1": "", "6_notes_2": "", "7_station_name": "कौशाम्बी", "8_station_name": "वैशाली"}}, "rapid_metro": {"stations": {"1_station_name": "सेक्टर ५५-५६", "2_station_name": "सेक्टर-५४ चौक", "3_station_name": "सेक्टर-५३-५४", "4_station_name": "सेक्टर-४२-४३", "5_station_name": "फेज-१", "6_station_name": "सिकंदरपुर", "6_notes_1": "", "6_notes_2": "", "7_station_name": "फेज-२", "8_station_name": "बेलवेडेरे टावर्स", "9_station_name": "साइबर सिटी", "10_station_name": "मौलसरी ावेनुए", "11_station_name": "फेज-३"}}}');

执行查询后会返回目标结果：blue_line_branch和rapid_metro。

内容的提问来源于stack exchange，提问作者Indark