MYSQL 5.7 One-time workaround for the json_table () function

Known as one of the most popular open source database, MySQL is widely used in various scenarios, ApsaraDB RDS for MySQL of Alibaba Cloud provided critical relief for companies, especially small and medium-sized enterprises affected by novel coronavirus (COVID-19).

First, preface

The database currently used for the project is Oracle 12c, and one of the main reasons for selecting that version is to support JSON. One day, a perverted customer said to move to the cloud platform, but the cloud platform does not have Oracle only MySQL and other open source products. One of the most annoying challenges in the process of converting a library script (including tables, views, indexes, triggers, stored procedures, and so on) from Oracle to MySQL is to replace the json_table () function.

The Oracle version of the script uses the Json_value () function in many places, with the json_table () in a few places. When you switch to MySQL, Json_value () can be replaced with json_extract (), but json_table () ... It is well known that MySQL's function cannot return a variable of the table type, so there is no corresponding function at all, and no corresponding idea is provided. Once through the Google search to a very complex example of SQL code, but did not understand at that time, there is no modification, and now even Google is not going, can only find another way out.

Second, Oracle Script

To remove unnecessary content, the Oracle script associated with Json_table () is 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"}
]

Third, MySQL script

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

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

The more difficult is the transformation function (the stored procedure is similar, the limit is less), after a series of attempts, using the cyclic value + comparison method to replace the method succeeds:

 
 
-- 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 ;

Less efficient, but also applicable on most occasions.

The most difficult is the transformation of the view, MySQL does not support table functions, the view definition can not have additional operations (such as dump to the temporary table), the first real helpless ...

Later learned that each JSON AddressType value range only six numbers, and within the unique, finally found an alternative method:

 
-- View CREATE OR REPLACE VIEW person_addr_view AS SELECT ID, ADDRESS_TYPE, ADDRESS_CODE, ADDRESS_DETAIL, ADDRESS_ZIP_CODE FROM ( SELECT PI.ID 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 PI.ID 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 PI.ID 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 PI.ID 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 PI.ID 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 PI.ID 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 all null rows from appearing, for ' $[n]. ' N, if greater than or equal to the value of Json_length (), json_extract () returns NULL.

This performance is too low to even feel embarrassed, as far as the scope of application is limited, changing a scene is difficult to apply. But after all, the project can be used without hurt – the foreground code (general modifications are still unavoidable).

Fourth. remarks

The above code has been measured, due to the small test environment data, performance data error is large, not given here;

MYSQL supports JSON from the beginning of the 12c starting from 5.7 json,oracle;
MYSQL 8 (Skip 6 and 7 directly) will support Json_table (), as well as other table functions.

MYSQL 5.7 One-time workaround for the json_table () function

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch