1 缘起与目的

最近在开发中频繁遇到json字符串存到了数据库表字段中，需要对该字段的json字符串进行检索和查询，发现网上文章参差不齐，这里梳理一下常用的json对象与数组的查询以供查阅，同时提供官方中文文档供读者取用。

2 实验数据搭建

这里为了方便大家查阅，直接以表格形式放测试数据，如果读者需要自行测试，自取SQL语句。

注意：以下瀚高查询依赖的ext_json_obj和ext_json_arr字段类型均为jsonb，字段类型不一致可能造成查询不到结果。

CREATE TABLE `test`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ext_json_arr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ext_json_obj` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 'AAA', '[{\"key\":\"A\",\"label\":\"A资金code\",\"value\":\"a\"},{\"key\":\"B\",\"label\":\"B资金code\",\"value\":\"b\"},{\"key\":\"C\",\"label\":\"C资金code\",\"value\":\"C\"}]', '{\"a\":\"a\",\"b\":\"b\",\"c\":[{\"o1\":\"oo1\",\"o2\":\"oo2\"},{\"k1\":\"kk1\",\"k2\":\"kk2\"}]}');
INSERT INTO `test` VALUES (2, 'BBB', '[{\"key\":\"A\",\"label\":\"A资金code\",\"value\":\"a\"},{\"key\":\"B\",\"label\":\"B资金code\",\"value\":\"b\"},{\"key\":\"D\",\"label\":\"D资金code\",\"value\":\"D\"}]', '{\"a\":\"a1\",\"x\":\"x\",\"y\":[{\"o1\":\"oo1\",\"o2\":\"oo2\"},{\"z1\":\"zz1\",\"z2\":\"zz2\"}]}');

    CREATE TABLE test.test(
        id integer NOT NULL,
        name character varying(255) COLLATE pg_catalog."default",
        ext_json_arr jsonb,
        ext_json_obj jsonb,
        PRIMARY KEY (id));

    
    insert into test.test (id,name,ext_json_arr,ext_json_obj) values(1,'AAA','[{"key":"A","label":"A资金code","value":"a"},{"key":"B","label":"B资金code","value":"b"},{"key":"C","label":"C资金code","value":"C"}]','{"a":"a","b":"b","c":[{"o1":"oo1","o2":"oo2"},{"k1":"kk1","k2":"kk2"}]}');
    insert into test.test (id,name,ext_json_arr,ext_json_obj) values(2,'BBB','[{"key":"A","label":"A资金code","value":"a"},{"key":"B","label":"B资金code","value":"b"},{"key":"D","label":"D资金code","value":"D"}]','{"a":"a1","x":"x","y":[{"o1":"oo1","o2":"oo2"},{"z1":"zz1","z2":"zz2"}]}');

3 实战演练

如果您对下文中任何函数以及用法有疑问，请参阅MySQL中文文档、PostgreSQL中文文档。

3.1 精确查询ext_json_obj中a的值为a的数据

• mysql

  SELECT
          * 
  FROM
          `test` 
  WHERE
          ext_json_obj ->> '$.a' = 'a';
  

• 瀚高

  SELECT 
     *
  FROM 
      test
  WHERE 
      ext_json_obj->> 'a' = 'a';
  

3.2 精确查询ext_json_obj中b的值为b的数据，并返回其中a的值

• MySQL

  SELECT
          ext_json_obj ->> '$.a' 
  FROM
          `test` 
  WHERE
          ext_json_obj ->> '$.b' = 'b';
  

• 瀚高

  SELECT
          ext_json_obj ->> 'a' 
  FROM
          `test` 
  WHERE
          ext_json_obj ->> 'b' = 'b';
  
  

3.3 精确查询json数组字段ext_json_arr中第一个元素key为A的数据

• MySQL

  SELECT
          * 
  FROM
          `test` 
  WHERE
          ext_json_arr -> '$[0].key' = 'A';
  

• 瀚高

  SELECT 
          * 
  FROM 
          test 
  WHERE 
          ext_json_arr - > 0 - >> 'key' = 'A' ; 
  

3.4 模糊查询json数组字段ext_json_arr中label中存在模糊匹配含D的数据

• MySQL

  SELECT
          * 
  FROM
          test 
  WHERE
          ext_json_arr ->> '$[*].label' LIKE '%D%';
  

• 瀚高

  方式1：联合查询方式

  SELECT DISTINCT t.*  
  FROM test t,  
       jsonb_array_elements(t.ext_json_arr) AS elem  
  WHERE elem->>'label' LIKE '%D%';
  

  方式2：子查询

  SELECT t.*  
  FROM test t  
  WHERE EXISTS (  
      SELECT 1  
      FROM jsonb_array_elements(t.ext_json_arr) AS elem  
      WHERE elem->>'label' LIKE '%D%'  
  );
  

3.5 精确查询json数组字段ext_json_arr中存在key为D的数据

• MySQL

  SELECT
          * 
  FROM
          test 
  WHERE
          JSON_CONTAINS(
          ext_json_arr,
          JSON_OBJECT( 'key', 'C' ));
  

• 瀚高

  方式1：联合查询方式

  SELECT DISTINCT t.*  
  FROM test t,  
       jsonb_array_elements(t.ext_json_arr) AS elem  
  WHERE elem->>'key' = 'D';
  

  方式2：子查询

  SELECT t.*  
  FROM test t  
  WHERE EXISTS (  
      SELECT 1  
      FROM jsonb_array_elements(t.ext_json_arr) AS elem  
      WHERE elem->>'key' = 'D'  
  );
  

  方式3：高级查询（版本不一定支持）

  SELECT *  
  FROM test  
  WHERE ext_json_arr @> '[{"key": "D"}]'::jsonb;