在 MySQL 5.7 中，如何在 json 字段上查询同一层级结构中的多个相同字段的值？

1、在 json 字段中的值如下所示。需要查询的字段：$.current.sections.announcement-bar.blocks.*.settings.text。其中 * 所对应的字段 key 是未知的。如图1

图1

{
  "current": {
    "sections": {
      "announcement-bar": {
        "type": "announcement-bar",
        "blocks": {
          "announcement-bar-0": {
            "type": "announcement",
            "disabled": false,
            "settings": {
              "link": "/",
              "text": "❤Free Shipping Over $100.0❤",
              "image": null,
              "text_color": "#ffffff",
              "mobile_text": "",
              "mobile_image": null,
              "background_color": "#000000"
            }
          },
          "6oaZmAscmONct-2pLAp5O": {
            "type": "announcement",
            "disabled": false,
            "settings": {
              "link": "/",
              "text": "<p>❤Free Shipping Over $100.0❤ 1</p>",
              "image": null,
              "text_color": "#ffffff",
              "mobile_text": "",
              "mobile_image": null,
              "background_color": "#000000"
            }
          },
          "JrFCMGW-EBnVZQ3sQT-WP": {
            "type": "announcement",
            "disabled": false,
            "settings": {
              "link": "/",
              "text": "<p>❤Free Shipping Over $100.0❤ 2</p>",
              "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
  }
}

2、最后整理的 SQL 如下，查询结果为数组，如果不存在，则为 NULL。如图2

图2

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'

["❤Free Shipping Over $100.0❤", "<p>❤Free Shipping Over $100.0❤ 1</p>", "<p>❤Free Shipping Over $100.0❤ 2</p>"]

3、announcement-bar 需要加上双引号，否则会报错：Invalid JSON path expression. The error is around character position 35.。如图3

图3