{"id":8442,"date":"2025-10-14T09:18:07","date_gmt":"2025-10-14T08:18:07","guid":{"rendered":"https:\/\/dasini.net\/blog\/?p=8442"},"modified":"2025-10-14T09:18:09","modified_gmt":"2025-10-14T08:18:09","slug":"querying-the-unstructured-natural-language-to-sql-for-json-data","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2025\/10\/14\/querying-the-unstructured-natural-language-to-sql-for-json-data\/","title":{"rendered":"Querying the Unstructured: Natural Language to SQL for JSON Data"},"content":{"rendered":"\n

In Ask Your Database Anything: Natural Language to SQL (NL2SQL) in MySQL HeatWave<\/em><\/code><\/strong><\/a>, we have explored the innovative MySQL HeatWave GenAI technology that converts Natural Language into SQL, making it easier for you to interact with databases. This feature collects information on the schemas, tables, and columns that you have access to, and then uses a Large Language Model (LLM) to generate an SQL query for the question pertaining to your data. It also lets you run the generated query and view the result set.<\/p>\n\n\n\n

Following our last article, Let Your AI DBA Assistant Write Your MySQL Queries<\/code><\/strong><\/em><\/a>, showcased an interesting use case: leveraging an AI DBA Assistant to generate monitoring and tuning queries for the Performance, Information, and Sys Schemas using plain English.<\/p>\n\n\n\n

In this article, we will talk about how to handle JSON documents with MySQL HeatWave GenAI NL2SQL<\/strong> feature.<\/p>\n\n\n

\n
\"\"\n\t\t\t\n\t\t\t\t\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n\n

Context<\/h2>\n\n\n\n

I’m using MySQL HeatWave<\/strong><\/a> 9.4.2<\/strong> on Oracle Cloud Infrastructure<\/a> (OCI):<\/p>\n\n\n\n

SQL> \n-- HeatWave MySQL server version\nSHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');\n+-----------------+--------------------------+\n| Variable_name   | Value                    |\n+-----------------+--------------------------+\n| version         | 9.4.2-cloud              |\n| version_comment | MySQL Enterprise - Cloud |\n+-----------------+--------------------------+<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Furthermore, I’m using a modified version (available on my GitHub account<\/a>, file: nl2sql_world_json_nested_-_tables_json_nested.sql<\/a><\/em>) of the well-known World database<\/strong><\/em><\/a>. Although the data hasn\u2019t been updated in some time, it still serves as a useful tool for visualizing the results of SQL queries.<\/p>\n\n\n\n
Here the schema:<\/p>\n\n\n\n
CREATE TABLE `country_json_nested` (\n  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies country_json JSON documents.',\n  `country_description` json NOT NULL COMMENT 'Country information. Including country names, continent, region, surface area, identifiers (country code), demographics, economy (GNP), and government structure',\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores detailed information about countries, including identifiers, demographics, economy, and government structure.'\n;\n\nCREATE TABLE `country_language_json_nested` (\n  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies country_language_json JSON documents.',\n  `country_language_description` json NOT NULL COMMENT 'Country languages information. Including a three-letter country code referencing the country table, the name of the language spoken in the country, whether they are official and the share of the population using them.',\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about the languages spoken in each country, including whether they are official and the share of the population using them.'\n;\n\nCREATE TABLE `city_json_nested` (\n  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies city_json JSON documents.',\n  `city_description` json NOT NULL COMMENT 'Cities information. including their name, country, district, and population.',\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about cities, including their name, country, district, and population.'\n;<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Let\u2019s run 3 queries on the data set:<\/p>\n\n\n\n
MySQL>\n-- Find the name, country code and the district of the city with ID 2989\nSELECT \n    city_description->>\"$.city_name\" AS \"City Name\", \n    city_description->>\"$.location.country_code\" AS \"Country Code\", \n    city_description->>\"$.location.district\" AS District \nFROM city_json_nested \nWHERE city_description->>\"$.ID_city\" = 2989;\n+-----------+--------------+-------------+\n| city_name | country_code | district    |\n+-----------+--------------+-------------+\n| Grenoble  | FRA          | Rh\u00f4ne-Alpes |\n+-----------+--------------+-------------+\n\n-- Find the government form of Germany\nSELECT \n    country_description->>\"$.government.government_form\" AS \"Government Form\" \nFROM country_json_nested \nWHERE country_description->>\"$.identifiers.code\" = 'FRA';\n+-----------------+\n| government_form |\n+-----------------+\n| Republic        |\n+-----------------+\n\n-- List all official languages spoken in Canada\nSELECT \n    country_language_description->>\"$.language_info.language\" AS Language \nFROM country_language_json_nested \nWHERE country_language_description->>\"$.country.country_code\" = 'CAN' \n    AND country_language_description->>\"$.language_info.is_official\" = 'T';\n+----------+\n| language |\n+----------+\n| English  |\n| French   |\n+----------+<\/code><\/pre>\n\n\n\n
I’m employing the inline path operator (->><\/a><\/code>)<\/strong>, which serves as a convenient shorthand for extracting and unquoting values: JSON_UNQUOTE<\/a>(JSON_EXTRACT<\/a>(...))<\/code>.<\/p>\n\n\n\n
<\/div>\n\n\n\n
The execution of these queries, while simple, still relies on existing SQL and JSON expertise<\/strong>. MySQL HeatWave GenAI can eliminates this dependency by enabling users to interact with the database via Natural Language to SQL<\/strong>. As highlighted in \u00ab\u00a0Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave<\/a>\u00a0\u00bb the new NL_SQL<\/code> routine<\/strong> allows non-technical users to write requests in plain English, and have the system automatically generate the necessary SQL.<\/p>\n\n\n\n
<\/div>\n\n\n
\n
\"\"<\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n\n
\n
Introducing JSON<\/h2>\n\n\n\n
JSON<\/a> (JavaScript Object Notation) is a lightweight, human-readable format for structuring and exchanging data. Built around key\u2013value pairs and ordered arrays, JSON enables developers to model complex, hierarchical information without the rigidity of a fixed schema. 
Its simplicity, flexibility, and near-universal support across programming languages have made it the standard for transmitting structured data between servers, web applications, and APIs in modern software systems.<\/p>\n<\/div><\/div>\n\n\n\n
<\/div>\n\n\n\n
JSON in the MySQL Context<\/h2>\n\n\n\n
In MySQL, the native JSON data type \u2014 introduced in version 5.7 \u2014 brings NoSQL-style flexibility into the realm of relational databases. It allows developers to store, validate, and optimize JSON documents directly within columns, combining the ACID compliance and transactional integrity of MySQL with the dynamic nature of semi-structured data. 
Specialized JSON functions such as JSON_EXTRACT<\/em><\/a>()<\/code>, JSON_CONTAINS<\/em><\/a>()<\/code>, JSON_OBJECT<\/em><\/a>() or JSON_TABLE<\/em><\/a>()<\/code> enable precise querying and manipulation, while indexing options like generated columns<\/a> ensure high performance. 
This hybrid approach lets MySQL handle everything from traditional records to complex API payloads within a unified, scalable system.<\/p>\n\n\n\n
Some MySQL & JSON content that you may find useful:<\/p>\n\n\n\n