In MySQL 5.7, how can I query the values of multiple same fields in the same hierarchy on the JSON field?

by 永夜 · 2023/11/22

浏览量： 14

1. The values in the JSON field are as follows. The fields to query: $.current.sections.announcement-bar.blocks.*.settings.text. The field key corresponding to * is unknown. as shown in Figure 1

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

Figure 1


{
  "current": {
    "sections": {
      "announcement-bar": {
        "type": "announcement-bar",
        "blocks": {
          "announcement-bar-0": {
            "type": "announcement",
            "disabled": false,
            "settings": {
              "link": "/",
              "text": "&#x2764;Free Shipping Over $100.0&#x2764;",
              "image": null,
              "text_color": "#ffffff",
              "mobile_text": "",
              "mobile_image": null,
              "background_color": "#000000"
            }
          },
          "6oaZmAscmONct-2pLAp5O": {
            "type": "announcement",
            "disabled": false,
            "settings": {
              "link": "/",
              "text": "&#x2764;Free Shipping Over $100.0&#x2764; 1",
              "image": null,
              "text_color": "#ffffff",
              "mobile_text": "",
              "mobile_image": null,
              "background_color": "#000000"
            }
          },
          "JrFCMGW-EBnVZQ3sQT-WP": {
            "type": "announcement",
            "disabled": false,
            "settings": {
              "link": "/",
              "text": "&#x2764;Free Shipping Over $100.0&#x2764; 2",
              "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. The final sql is as follows, the query result is an array, if it does not exist, it is null. as shown in Figure 2

Figure 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'


["&#x2764;Free Shipping Over $100.0&#x2764;", "&#x2764;Free Shipping Over $100.0&#x2764; 1", "&#x2764;Free Shipping Over $100.0&#x2764; 2"]

3. Announcement-bar needs to add double quotation marks, otherwise an error will be reported: Invalid json path expression. The error is around character position 35. as shown in Figure 3

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

Figure 3

In MySQL 5.7, how can I query the values of multiple same fields in the same hierarchy on the JSON field?

You may also like...

Leave a Reply Cancel reply

In MySQL 5.7, how can I query the values of multiple same fields in the same hierarchy on the JSON field?

You may also like...

Http client extension based on Yii 2 under Windows 10 64 bit, requesting HTTPS, error: “CURL ERROR: #60 – SSL Certificate Problem: Unablem: Unable To get local issuer certificate”

In spatie\QueryBuilder , the request parameter contains “,” error: sqlstate[HY093]: Invalid parameter number: number of bound variables not match number of tokens

Getting Started with Go Language: Closure Implementing the Fibonacci Sequence Generator

Leave a Reply Cancel reply