大佬们，为什么我查不出来结果呢？

Question

一张表，结构简单

CREATE TABLE `zp_category_relation` (
  `id` bigint(20) NOT NULL,
  `aid` bigint(20) NOT NULL COMMENT '文章 id',
  `cid` bigint(20) NOT NULL COMMENT '分类 id',
  `extend` json DEFAULT NULL COMMENT '扩展字段',
  PRIMARY KEY (`id`),
  UNIQUE KEY `acr_ac_uidx` (`aid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类的关系';

其中一条数据为：

INSERT INTO `zp_category_relation` (`id`, `aid`, `cid`, `extend`) VALUES (393388343615493, 393060164034565, 393041089290245, '{\"393041089290245\": [{\"key\": \"type\", \"value\": \"3\"}]}');

想查出 extend 列中，key=type 的数据，用的如下 sql 语句

SELECT * FROM `zp_category_relation` WHERE  extend->"$[*].key" = "type";

结果为空，求教更准确的 sql 语句

Answer 1

这样可以做到准确的搜索，但是效率不是很高。

SELECT * FROM `zp_category_relation` WHERE  JSON_SEARCH(extend, 'one', "type", '', "$.*[*].key");

$.* 这表示 393041089290245 这一层
$.*[*] 则表示 393041089290245 这一层下面的数组
$.*[*].key 则表示数组下的 key 了，因为父级是数组，所以返回的结果也是一个数组。

但是如果你这里 393041089290245 下面的数组只有一个值，则可以把 [*] 换成 [0]，然后直接在后面等于。 extend->>"$.*[0].key" = '["type"]'。

SELECT * FROM `zp_category_relation` WHERE extend->>"$.*[0].key" = '["type"]';

另外还可考虑使用虚拟列。