在 MySQL 中获取特定值的 Json 键 [英] Get Json keys in a MySQL for a particular value

问题描述

我刚刚安装了 MySQL 5.7.27，我想使用一些 Json 字段，所以我创建了一些记录，例如字段中的这个值:

I just installed MySQL 5.7.27 and I would like to use some Json fields so, I created some records, for example this value in a field:

{
  "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"
  }
}

我想知道如何检索包含 "is_filter":true 的元素的键，以及关联的名称"

I would like to know how can I retrieve keys of elements which contain "is_filter":true, dans the associated "name"

我尝试过 JSON_SEARCH、JSON_EXTRACT，但我觉得我用得不太好.

I tried with JSON_SEARCH, JSON_EXTRACT but I think I don't use it well.

例如，对于这个值，我希望输出是:

For example, for this value, I expect the output will be:

Key: Intitule
Name: Intitule de la formation

因为 is_filter 为真，但不适用于Fin"

Because is_filter is true, but not for "Fin"

感谢您的帮助！

推荐答案

就像我说的，当您在这里处理基于文本的键时，在 MySQL 中解析 JSON 可能是一个挑战.
因此，您需要使用 JSON_KEYS() 将它们与数字生成器结合使用，生成动态 JSON 路径以用于 JSON_EXTRACT()

Like i said, it can be a challenge parsing JSON in MySQL as you are dealing with text based keys here.
So you would need to use JSON_KEYS() to get those in combination with a number generator a dynamic JSON path is generated to be used in JSON_EXTRACT()

MySQL 的 8 函数 JSON_TABLE() 使它变得更容易..

MySQL 's 8 function JSON_TABLE() makes it much much more easy..

请记住，这个答案纯粹是为了教育乐趣

Bill Karwin 的评论

您应该以不同的方式构建您的 JSON 以支持搜索你需要做的，否则你不应该使用 JSON.只需存储多行数据，然后SELECT * FROM mytable WHERE is_filter = true -

You should either structure your JSON differently to support the search you need to do, or else you should not use JSON. Just store the data in multiple rows, and then SELECT * FROM mytable WHERE is_filter = true -

查询

 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 

结果

| name                     |
| ------------------------ |
| Intitule de la formation |

参见演示

这篇关于在 MySQL 中获取特定值的 Json 键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！