MySql 5.7 A workaround for json

1. Preface

The database currently used by the project isOracle12c, one of the main reasons for choosing this version is to support json. One day, a perverted customer said he wanted to turn to a cloud platform, but the cloud platform did not have Oracle but only MySql and other open source products. In the process of converting library creation scripts (including tables, views, indexes, triggers, stored procedures, etc.) from oracle to mysql version, the most annoying difficulty is how to implement json_table()functionalternative.

Oracle version scripts use the json_value() function in many places, and a few use json_table(). Turn toMySqlIn version, json_value() can be replaced with json_extract(), but json_table()... As we all know, MySql functions cannot return table-type variables, so there is no corresponding function at all, and no corresponding ideas are provided. I once searched for a very complex example SQL code through Google, but I really couldn't understand it at that time, so I couldn't talk about transformation. Now I can't even google, so I can only find another way out.

2. Oracle Scripts

Remove unnecessary content, and the Oracle version scripts related to json_table() are as follows:

-- Table
create table PERSON_INFO 
(
   ID          NUMBER(15)           not null ,
   ADDR_INFO   VARCHAR2(1000) CONSTRAINT ADDR_INFO_JSON CHECK (ADDR_INFO IS JSON),
   PRIMARY KEY ( ID )
);

-- View
CREATE OR REPLACE VIEW PERSON_ADDR_VIEW
AS
 SELECT
   PI.ID ID,
   TEMP_TAB.ADDRESS_CODE,
   TEMP_TAB.ADDRESS_DETAIL,
   TEMP_TAB.ADDRESS_TYPE,
   TEMP_TAB.ADDRESS_ZIP_CODE
 FROM PERSON_INFO PI, 
  JSON_TABLE(PI.ADDR_INFO, '$[*]' COLUMNS (ADDRESS_CODE VARCHAR2 PATH '$.AddressCode', ADDRESS_DETAIL VARCHAR2 PATH '$.AddressDetail',
             ADDRESS_TYPE VARCHAR2 PATH '$.AddressType', ADDRESS_ZIP_CODE VARCHAR2 PATH '$.AddressZipCode')) TEMP_TAB;

-- Function
CREATE OR REPLACE 
FUNCTION GET_ADDR_NAME_BY_TYPE (P_ADDR_INFO IN VARCHAR2, P_ADDR_TYPE IN NUMBER)
RETURN VARCHAR2 AS 
RET_ADDR_NAME VARCHAR2(200);
BEGIN
  IF P_ADDR_INFO IS NULL THEN RETURN '0' ;END IF;
  SELECT T.ADDRESS_NAME INTO RET_ADDR_NAME 
    FROM JSON_TABLE(P_ADDR_INFO, '$[*]' COLUMNS (ADDRESS_NAME VARCHAR2 PATH '$.AddressDetail', ADDRESS_TYPE VARCHAR2 PATH '$.AddressType')) T  
    WHERE T.ADDRESS_TYPE = P_ADDR_TYPE;
  RETURN  RET_ADDR_NAME;
END HS_GET_ADDR_NAME_BY_TYPE;
/

An example of a json field:

[{"AddressType":1, "AdrressCode":"Code 1", "AdreessDetail":"aaaa", "AddressZipCode":"100010"},
 {"AddressType":2, "AdrressCode":"Code 2", "AdreessDetail":"bbbb", "AddressZipCode":"200020"},
 {"AddressType":5, "AdrressCode":"Code 1", "AdreessDetail":"xxxx", "AddressZipCode":"500050"}
]

3. MySql script

The easiest thing is to transform the table and directly change the field type to JSON:

-- Table
create table PERSON_INFO 
(
   ID          DECIMAL(15)           not null ,
   ADDR_INFO   JSON,
   PRIMARY KEY ( ID )
);

The more difficult one is to transform the function (storage procedures are similar, with fewer restrictions). After a series of attempts, the method of loop value + comparison is successfully replaced by the method:

-- Function
DELIMITER /

DROP FUNCTION IF EXISTS GET_ADDR_NAME_BY_TYPE/

CREATE FUNCTION GET_ADDR_NAME_BY_TYPE (P_ADDR_INFO VARCHAR(1000), P_ADDR_TYPE DECIMAL)
RETURNS VARCHAR(200)
BEGIN
   DECLARE RET_ADDR_NAME VARCHAR(200);
   DECLARE RET_ADDR_TYPE int;
   DECLARE n int;
   DECLARE i int;
   IF P_ADDR_INFO IS NULL THEN RETURN '0' ;END IF;
   -- SELECT T.ADDRESS_NAME INTO RET_ADDR_NAME FROM JSON_TABLE(P_ADDR_INFO, '$[*]' COLUMNS (ADDRESS_NAME VARCHAR2 PATH '$.AddressDetail', ADDRESS_TYPE VARCHAR2 PATH '$.AddressType')) T  WHERE T.ADDRESS_TYPE = P_ADDR_TYPE;
   SELECT json_length(P_ADDR_INFO) into n;
   set i = 0;
   WHILE i<n DO
      SELECT json_extract(P_ADDR_INFO, concat('$[', i, '].AddressDetail')), 
             json_extract(P_ADDR_INFO, concat('$[', i, '].AddressType')) 
      INTO RET_ADDR_NAME, RET_ADDR_TYPE FROM DUAL;
      
      IF RET_ADDR_TYPE=P_ADDR_TYPE THEN return RET_ADDR_NAME; END if;
      set i = i+1;
   END WHILE;
   RETURN '0';
END;
/

DELIMITER ;

It is less efficient, but it is also applicable in most occasions.

The most difficult thing is to transform the view. MySql does not support table functions, and there cannot be additional operations in the VIEW definition (such as transferring to temporary tables). It is really helpless at the beginning...

Later I learned that the value range of AddressType in each json is only six numbers, and it is unique internally, and I finally found an alternative:

-- View
CREATE OR REPLACE VIEW person_addr_view
AS
SELECT ID, ADDRESS_TYPE, ADDRESS_CODE, ADDRESS_DETAIL, ADDRESS_ZIP_CODE
FROM 
   (
   SELECT
       ID, 
      json_extract(PI.ADDR_INFO, '$[0].AddressType') ADDRESS_TYPE, 
      json_extract(PI.ADDR_INFO, '$[0].AddressCode') ADDRESS_CODE, 
      json_extract(PI.ADDR_INFO, '$[0].AddressDetail') ADDRESS_DETAIL,
      json_extract(PI.ADDR_INFO, '$[0].AddressZipType') ADDRESS_ZIP_CODE
   FROM MIS_PERSON_TEXT_INFO PI
   UNION ALL
   SELECT
       ID, 
      json_extract(PI.ADDR_INFO, '$[1].AddressType') ADDRESS_TYPE, 
      json_extract(PI.ADDR_INFO, '$[1].AddressCode') ADDRESS_CODE, 
      json_extract(PI.ADDR_INFO, '$[1].AddressDetail') ADDRESS_DETAIL,
      json_extract(PI.ADDR_INFO, '$[1].AddressZipType') ADDRESS_ZIP_CODE
   FROM MIS_PERSON_TEXT_INFO PI
   UNION ALL
   SELECT
       ID, 
      json_extract(PI.ADDR_INFO, '$[2].AddressType') ADDRESS_TYPE, 
      json_extract(PI.ADDR_INFO, '$[2].AddressCode') ADDRESS_CODE, 
      json_extract(PI.ADDR_INFO, '$[2].AddressDetail') ADDRESS_DETAIL,
      json_extract(PI.ADDR_INFO, '$[2].AddressZipType') ADDRESS_ZIP_CODE
   FROM MIS_PERSON_TEXT_INFO PI
   UNION ALL
   SELECT
       ID, 
      json_extract(PI.ADDR_INFO, '$[3].AddressType') ADDRESS_TYPE, 
      json_extract(PI.ADDR_INFO, '$[3].AddressCode') ADDRESS_CODE, 
      json_extract(PI.ADDR_INFO, '$[3].AddressDetail') ADDRESS_DETAIL,
      json_extract(PI.ADDR_INFO, '$[3].AddressZipType') ADDRESS_ZIP_CODE
   FROM MIS_PERSON_TEXT_INFO PI
   UNION ALL
   SELECT
       ID, 
      json_extract(PI.ADDR_INFO, '$[4].AddressType') ADDRESS_TYPE, 
      json_extract(PI.ADDR_INFO, '$[4].AddressCode') ADDRESS_CODE, 
      json_extract(PI.ADDR_INFO, '$[4].AddressDetail') ADDRESS_DETAIL,
      json_extract(PI.ADDR_INFO, '$[4].AddressZipType') ADDRESS_ZIP_CODE
   FROM MIS_PERSON_TEXT_INFO PI
   UNION ALL
   SELECT
       ID, 
      json_extract(PI.ADDR_INFO, '$[5].AddressType') ADDRESS_TYPE, 
      json_extract(PI.ADDR_INFO, '$[5].AddressCode') ADDRESS_CODE, 
      json_extract(PI.ADDR_INFO, '$[5].AddressDetail') ADDRESS_DETAIL,
      json_extract(PI.ADDR_INFO, '$[5].AddressZipType') ADDRESS_ZIP_CODE
   FROM PERSON_INFO PI
   ) union_tab
   WHERE ADDRESS_TYPE IS NOT NULL;

The final WHERE condition is to prevent the occurrence of all NULL lines. For n in '$[n].', if it is greater than or equal to the value of json_length(), json_extract() returns NULL.

thisperformanceIt’s so low that I feel embarrassed that I feel. As for the scope of application, it’s even more limited. It’s hard to say that it can be applied in another scenario. But after all, the project can be used, so there is no need to hurt the front desk code (generally, modifications are still inevitable).

4. Notes

All the above codes have passed actual testing. Due to the small amount of data in the test environment and the large error in performance data, they will not be given here;
MySql supports json since 5.7, and Oracle supports json since 12c;
MySql 8 (skip 6 and 7 directly) will support json_table(), as well as other table functions.

MySql 5.7 A workaround for json_table function

1. Preface

2. Oracle Scripts

3. MySql script

4. Notes