微信技术群：Day9884125

一 背景

   一般在开发中，会遇到数据库某个字段存取一个json串，然后我们需要提取出来。表中所有数据样式如下图

select * from test_json

1.1 按条件查询一条数据

SELECT JSON_EXTRACT(obj, '$[*].value') AS 注册号值
FROM test_json
WHERE JSON_SEARCH(obj, 'one', '华为', NULL, '$[*].name') IS NOT NULL;

1.2 按条件查询出多条数据

SELECT JSON_EXTRACT(obj, '$[*].value') AS 注册号值
FROM test_json
WHERE JSON_SEARCH(obj, 'one', '小米', NULL, '$[*].name') IS NOT NULL;

1.3 查询出结果，和列的内容

SELECT obj, JSON_EXTRACT(obj, '$[*].value') AS 注册号值
FROM test_json
WHERE JSON_SEARCH(obj, 'one', '小米', NULL, '$[*].name') IS NOT NULL;

二 json中是个数组的操作

   构造数据之后，数据库中数据的样子

SELECT obj, JSON_EXTRACT(obj, '$[*].value') AS 注册号值
FROM test_json
WHERE JSON_SEARCH(obj, 'one', '三星', NULL, '$[*].name') IS NOT NULL;

   查询出json中name为三星的value

SELECT 
JSON_EXTRACT(obj, 
concat(substring_index(trim(BOTH '"' FROM JSON_SEARCH(obj, 'one', '三星', NULL, '$[*].name')), '.', 1), '.value')) as 注册号
FROM test_json
WHERE JSON_SEARCH(obj, 'one', '三星', NULL, '$[*].name') IS NOT NULL;

   但是这个sql有一些问题，只能默认查询出第一条数据。假如数据库中三星的数据有两条，它只会查询出第一条数据，第二条数据会丢失。
数据库中的数据 正确结果

SELECT  
    jt.value as 注册号,tj.obj		
FROM  
    test_json tj,  
    JSON_TABLE(CAST(tj.obj AS JSON), '$[*]' COLUMNS(name VARCHAR(255) PATH '$.name', value VARCHAR(255) PATH '$.value')) jt  
WHERE  
    jt.name = '三星';