在 MySQL 5.7 中，如何在 json 字段上查询同一层级结构中的多个相同字段的值？
1、在 json 字段中的值如下所示。需要查询的字段：$.current.sections.announcement-bar.blocks.*.settings.text。其中 * 所对应的字段 key 是未知的。如图1
图1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | { "current": { "sections": { "announcement-bar": { "type": "announcement-bar", "blocks": { "announcement-bar-0": { "type": "announcement", "disabled": false, "settings": { "link": "/", "text": "<img draggable="false" role="img" class="emoji" alt=" "image": null, "text_color": "#ffffff", "mobile_text": "", "mobile_image": null, "background_color": "#000000" } }, "6oaZmAscmONct-2pLAp5O": { "type": "announcement", "disabled": false, "settings": { "link": "/", "text": "<p><img draggable="false" role="img" class="emoji" alt=" "image": null, "text_color": "#ffffff", "mobile_text": "", "mobile_image": null, "background_color": "#000000" } }, "JrFCMGW-EBnVZQ3sQT-WP": { "type": "announcement", "disabled": false, "settings": { "link": "/", "text": "<p><img draggable="false" role="img" class="emoji" alt=" "image": null, "text_color": "#ffffff", "mobile_text": "", "mobile_image": null, "background_color": "#000000" } } }, "disabled": false, "settings": { "sticky": false, "homepage_only": false }, "block_order": [ "announcement-bar-0", "6oaZmAscmONct-2pLAp5O", "JrFCMGW-EBnVZQ3sQT-WP" ] } }, "radius__image": 5, "radius__button": 6 }} |
" src="2、最后整理的 SQL 如下，查询结果为数组，如果不存在，则为 NULL。如图2
图2
1 2 3 4 5 6 7 | SELECT JSON_EXTRACT( `schema`, '$.current.sections."announcement-bar".blocks.*.settings.text' ) FROM `theme_asset2` WHERE `theme_id` = '9a1ce422-a2cc-4559-9c9b-2edd1c50db87' AND `asset_key` = 'config/settings_data.json' |
1 | ["<img draggable="false" role="img" class="emoji" alt=" |
3、announcement-bar 需要加上双引号，否则会报错：Invalid JSON path expression. The error is around character position 35.。如图3
图3
近期评论