Tuning a SQL query is a mix of data driven discovery and magic art, until now… Aiven SQL query optimizer allows you to speed up your query by simply pasting the SQL and additional metadata information. Check it out at https://go.aiven.io/ft-sql-query-optimizer

You can query a JSON document in MySQL to find content within it with:

• The JSON_CONTAINS function that will return if a JSON document is contained within another JSON document. The function returns 1 if the document is contained, 0 elsewhere.
• The JSON_CONTAINS function that will return if a JSON document path is contained within another JSON document. The function returns 1 if the document is contained, 0 elsewhere.

One of the most common tasks with strings is concatenation! This blog post showcases several techniques to perform it with MySQL.

Earlier this month I wrote a piece on solving Gunnar Morling interesting 1 billion rows challenge in PostgreSQL and ClickHouse. Since Aiven provides also MySQL, I wanted to give it a try. TLDR; The results are much slower than PG and ClickHouse, do you have any suggestion on how to improve?

You can tabulate a JSON document (retrieve it as a row) in MySQL with the JSON_TABLE function.

You can remove a field from JSON document in MySQL with the JSON_REMOVE function.

You can insert a field in a JSON document in MySQL with the JSON_INSERT function.

You can edit a JSON document in MySQL with:

• The JSON_SET function that will replace values for JSON paths that exists and add values for the ones that don’t exist.
• The JSON_REPLACE function that will replace values for JSON paths that exists and ignore the ones that don’t exist.

You can merge two JSON documents in MySQL with:

• the JSON_MERGE_PRESERVE function to concatenate the document values
• the JSON_MERGE_PATCH function to keep the latest value for each key

You can create a JSON document from fields in Mysql® with the JSON_OBJECT function

To get the type of a JSON item in MySQL you need to use the JSON_TYPE function.

To extract an item from an array in MySQL you need to use the -> operator and the [item_number] JSON Path Syntax.

MySQL® offers three ways to extract fields from a JSON object:

• the -> operator to extract the field as JSON
• the ->> operator to extract the field as text
• the JSON_EXTRACT function

Both operators use the JSON Path Syntax

To load JSON data into a MySQL column you need to include it as string.

This series covers how to solve common problems on JSON datasets with MySQL and it includes (links will appear once the target pages are up):