如图所示。

表中有一个字段extension,存储json串。

我想查询出extension字段值中，alias对应的值中含123字符串的数据行，sql可以怎么写？

sql如下所示：

/*

Navicat MySQL Data Transfer

Source Server : localhost_3306

Source Server Version : 50540

Source Host : localhost:3306

Source Database : test_json

Target Server Type : MYSQL

Target Server Version : 50540

File Encoding : 65001

Date: 2019-12-13 23:05:26

*/

SET FOREIGN_KEY_CHECKS=0;

– Table structure for scan_meta

DROP TABLE IF EXISTS scan_meta;

CREATE TABLE scan_meta (

id bigint(20) NOT NULL AUTO_INCREMENT,

name varchar(255) DEFAULT NULL,

type varchar(255) DEFAULT NULL,

env varchar(255) DEFAULT NULL,

extension varchar(255) DEFAULT NULL,

project varchar(255) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

– Records of scan_meta

INSERT INTO scan_meta VALUES (‘1’, ‘hd_hose’, ‘mysql’, ‘dev’, ‘{“instanceId”:“24729”,“dbId”:“116542”,“tableId”:“163666”,“alias”:“测试VPC_IDC”,“columnCount”:24}’, ‘hd_hose’);

INSERT INTO scan_meta VALUES (‘2’, ‘hd_hose’, ‘mysql’, ‘dev’, ‘{“instanceId”:“247292”,“dbId”:“116542”,“tableId”:“163666”,“alias”:“测试123VPC_IDC”,“columnCount”:24}’, ‘hd_hose’);

INSERT INTO scan_meta VALUES (‘3’, ‘hd_horse’, ‘mysql’, ‘dev’, ‘{“instanceId”:“247242”,“dbId”:“116542”,“tableId”:“164666”,“alias”:“测试1253VPC_IDC”,“columnCount”:24}’, ‘hd_hose’);

INSERT INTO scan_meta VALUES (‘4’, ‘hd_horse’, ‘mysql’, ‘dev’, ‘{“instanceId”:“247242”,“dbId”:“116542”,“tableId”:“164666”,“alias”:“测试12353VPC_IDC”,“columnCount”:24}’, ‘hd_123hose’);

INSERT INTO scan_meta VALUES (‘5’, ‘hd_horse’, ‘mysql’, ‘dev’, ‘{“instanceId”:“247242”,“dbId”:“116542”,“tableId”:“164666”,“alias”:“测试126353VPC_IDC”,“columnCount”:24}’, ‘hd_123hose’);

INSERT INTO scan_meta VALUES (‘7’, ‘hd_horse’, ‘mysql’, ‘dev’, ‘{“instanceId”:“247242”,“dbId”:“1236542”,“tableId”:“164666”,“alias”:“测试53VPC_IDC”,“columnCount”:24}’, ‘hd_13hose’);

请各位大神帮忙。