python - 查询匹配的 json 数组键

Question

我在 mysql 中有一个名为column_info. 这是一个示例值：

[{"id": ["132605", "132750", "132772", "132773", "133065", "133150", "133185", "133188", "133271", "133298"]}, 
 {"number": ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10"]}, 
 {"id": ["1", "1", "1", "1", "1", "1", "1", "1", "1", "1"]}
]

是否可以执行以下查询：

keys = ['id', 'number', 'id']
SELECT * FROM `column_info` WHERE JSON(??) = keys

标签： pythonmysqlsql

Answer 1

尝试：

SELECT `column_info`
FROM
  `table`,
  (SELECT @`keys` := '["id", "number", "id"]') `init`
WHERE (
  SELECT
    JSON_ARRAYAGG(
      JSON_UNQUOTE(
        JSON_EXTRACT(
          JSON_KEYS(`der`.`keys`),
          '$[0]'
        )
      )  
    )
  FROM
    JSON_TABLE(
      `column_info`,
      '$[*]'
      COLUMNS(
        `keys` JSON PATH '$'
      )  
    ) `der`
) = CAST(@`keys` AS JSON);

请参阅db-fiddle。