Overview

MySQL offers an efficient and easy to use JSON query processing natively within the relational database framework. The same capability is available in MySQL HeatWave. Developers have flexibility to either employ the conventional SQL interface or opt for the document-centric CRUD APIs. Applications written in high level programming languages such as Java, or .Net can use MySQL connectors to connect to MySQL HeatWave, while MySQL Shell provides an intuitive scripting interface.

Figure 1: Document store architecture of MySQL HeatWave

We are excited to introduce the JSON support in HeatWave which offers significant performance improvement with no change in applications, or no indexes needed for accelerating JSON processing. This provides significant enhancement for applications that heavily rely on JSON data structures, as query execution times are now drastically reduced, improving overall application performance. The JSON support in HeatWave provides the same benefits as that for scalar data types – real time analytics, in-memory compression, and order of magnitude of query acceleration.

TPCH_JSON_512

MySQL (sec)

HeatWave (sec)

HeatWave Speedup

Simple Filter Queries

5200

240

20x

Aggregation Queries

5500

250

22x

Large Join Queries

>10 hrs

300

144x

 

Compared to MySQL, simple filter queries and aggregation queries is 20X faster, and queries with large joins is 144X faster than MySQL.

Native JSON Support in HeatWave

HeatWave is an in-memory query accelerator, data stored in MySQL InnoDB is transformed to a hybrid-columnar format and stored in HeatWave memory.

To store and process JSON data efficiently, HeatWave introduces its own native JSON data type format, which is a binary and compressed representation of JSON. JSON column is managed like any standard columns, it is stored in a hybrid-columnar representation in Heatwave memory. Similarly, any updates in the JSON data stored in MySQL will be automatically propagated to HeatWave without user intervention. During query processing, JSON operators are executed directly on compressed binary representation of the JSON data structures.

Following JSON functions are currently supported in HeatWave:

#

Function

Description

1

column->path

Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().

2

column->>path

Return value from JSON column after evaluating path and unquoting the result; equivalent toJ SON_UNQUOTE(JSON_EXTRACT())

3

JSON_ARRAY()

Create JSON array

4

JSON_DEPT()

Maximum depth of JSON document

5

JSON_EXTRACT()

Return data from JSON document

6

JSON_LENGTH()

Number of elements in JSON document

7

JSON_OBJECT()

Create JSON object

8

JSON_UNQUOTE()

Unquote JSON value

 

Queries use the supported JSON functions and other supported SQL constructs get offloaded to HeatWave for query acceleration.

 

Example: JSON Query Processing with JavaScript using MySQL Shell

In this example, a 1GB TPC-H benchmark data and schema are converted to JSON data and format, where each row of every TPCH table is converted into a JSON document. The data set is then imported and loaded into a MySQL HeatWave instance.

Figure 2: LINEITEM table is converted to LINEITEM_JSON. LINEITEM_JSON has only one column, where each row of this table is a JSON document and whose fields are columns of the original LINEITEM table

 

To run query using MySQL Shell with MySQL X-Protocol:

  1. Connect to MySQL HeatWave instance

const session = mysqlx.getSession({ host: ‘heatwave.sub11071646451.demovcn.oraclevcn.com’, user: ‘admin’, port: ‘33060’})

const query = session.sql(“set use_secondary_engine=ON;”).execute();

const schema = session.getSchema(“tpch_1_json”);

 

  1. Run a simple query using X-protocol interface: Group By over the name in REGION table and return the count

var table_region=schema.getTable(‘REGION_JSON’)

var q1 = table_region.select(“INFO->’$.R_NAME'”).groupBy(“INFO->’$.R_NAME'”).execute().fetchAll();

 

  1. Run a join query using X-protocol interface: Query quantifies the amount of revenue increase from eliminating certain discounts in a given percentage range in a given year

session.sql(“USE tpch_1_json”).execute();

var q1=session.sql(“select sum(LINEITEM_JSON.INFO->’$.L_EXTENDEDPRICE’ * LINEITEM_JSON.INFO->’$.L_DISCOUNT’) as revenue from LINEITEM_JSON where LINEITEM_JSON.INFO->’$.L_SHIPDATE’ >= date ‘1994-01-01′ AND LINEITEM_JSON.INFO->’$.L_SHIPDATE’ < date ‘1994-01-01’ + interval ‘1’ year AND LINEITEM_JSON.INFO->’$.L_DISCOUNT’ between 0.06 – 0.01  AND  0.06 + 0.01 AND LINEITEM_JSON.INFO->’$.L_QUANTITY’ < 24;”).execute().fetchAll();

 

  1. Revenue from each order in selected period with range of discount presented in JSON format using JSON_OBJECT

session.sql(“USE tpch_1_json”).execute();

var q1=session.sql(“WITH base_table AS (select CAST(LINEITEM_JSON.INFO->>’$.L_ORDERKEY’ as DOUBLE) as order_key, sum(LINEITEM_JSON.INFO->’$.L_EXTENDEDPRICE’ * LINEITEM_JSON.INFO->’$.L_DISCOUNT’) as revenue_per_order from LINEITEM_JSON where LINEITEM_JSON.INFO->’$.L_SHIPDATE’ >= date ‘1994-01-01′ AND LINEITEM_JSON.INFO->’$.L_SHIPDATE’ < date ‘1994-01-01’ + interval ‘1’ year AND LINEITEM_JSON.INFO->’$.L_DISCOUNT’ between 0.06 – 0.01  AND  0.06 + 0.01 AND LINEITEM_JSON.INFO->’$.L_QUANTITY’ < 24 GROUP BY order_key) select JSON_OBJECT(“order_key”, order_key, “revenue_per_order”, revenue_per_order) FROM base_table;”).execute().fetchAll();

 

  1. Example query showing usage of JSON_UNQOUTE

session.sql(“USE tpch_1_json”).execute();

var q1=session.sql(“select JSON_OBJECT(“order_key”, LINEITEM_JSON.INFO->>’$.L_ORDERKEY’, “discount_per_order”, sum(LINEITEM_JSON.INFO->’$.L_EXTENDEDPRICE’ * LINEITEM_JSON.INFO->’$.L_DISCOUNT’)) as revenue_per_order from LINEITEM_JSON where LINEITEM_JSON.INFO->’$.L_SHIPDATE’ >= date ‘1994-01-01′ AND LINEITEM_JSON.INFO->’$.L_SHIPDATE’ < date ‘1994-01-01’ + interval ‘1’ year AND LINEITEM_JSON.INFO->’$.L_DISCOUNT’ between 0.06 – 0.01  AND  0.06 + 0.01 AND LINEITEM_JSON.INFO->’$.L_QUANTITY’ < 24;LINEITEM_JSON.INFO->’$.L_EXTENDEDPRICE’ GROUP BY LINEITEM_JSON.INFO->>’$.L_ORDERKEY’; “).execute().fetchAll();

 

Summary

MySQL provides both standard SQL and CRUD APIs for processing JSON data, enabling developers to develop applications to use relational data with semi-structured data stored in JSON format seamlessly. With the native JSON processing support, MySQL HeatWave provides significant query acceleration for JSON queries, enabling developers to develop applications to use JSON data for complex analytics use cases.