MySQL

In MySQL, JSON functions allow you to store, retrieve, and manipulate JSON data directly within the database. This feature is particularly useful when dealing with semi-structured data that doesn’t fit neatly into traditional relational tables. Using the SELECT statement with JSON functions, you can extract and manipulate JSON values stored in columns. MySQL provides a set of JSON functions, such as JSON_EXTRACT(), JSON_UNQUOTE(), and -> or ->> operators, to help work with JSON data effectively.

Here’s how to use JSON functions in MySQL with SELECT queries, along with examples:

1. Selecting a JSON Field:

   • To select JSON data from a column, you can directly retrieve the JSON column as part of the result.
   • Syntax:

   SELECT employee_details 
   FROM employees;

   • This query retrieves all the JSON data from the employee_details column.

2. Extracting Specific JSON Values with JSON_EXTRACT():

   • The JSON_EXTRACT() function allows you to retrieve specific values from a JSON object. You provide the JSON column and the path to the value you want to extract.
   • Example:

   SELECT JSON_EXTRACT(employee_details, '$.first_name') AS first_name 
   FROM employees;

   • This query extracts the first_name from the employee_details JSON object in the employees table.

   Additional Points:

   • The $. symbol indicates the root of the JSON object, and you can use dot notation to access nested fields.

3. Simplifying Extraction with -> Operator:

   • MySQL provides a shorthand operator -> to extract values from JSON columns. The -> operator returns a JSON value, while ->> returns a scalar value.
   • Example:

   SELECT employee_details->'$.first_name' AS first_name 
   FROM employees;

   • This query is equivalent to the previous one but uses the shorthand -> operator to extract the first_name.

4. Unquoting JSON Values with JSON_UNQUOTE():

   • When extracting JSON values, you may need to remove extra quotes from the result. Use the JSON_UNQUOTE() function to return unquoted JSON values.
   • Example:

   SELECT JSON_UNQUOTE(employee_details->'$.first_name') AS first_name 
   FROM employees;

   • This query extracts the first_name and removes the quotes around the value.

5. Extracting Nested JSON Fields:

   • You can extract deeply nested JSON values by providing the full path in the JSON_EXTRACT() function or the -> operator.
   • Example:

   SELECT JSON_UNQUOTE(employee_details->'$.address.city') AS city 
   FROM employees;

   • This query retrieves the city field from the address object inside the employee_details JSON column.

6. Filtering Data Using JSON Values in WHERE Clause:

   • You can filter rows based on JSON values by using JSON_EXTRACT() or the -> operator in the WHERE clause.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE JSON_UNQUOTE(employee_details->'$.department') = 'HR';

   • This query returns employees who work in the HR department by filtering based on the department value inside the employee_details JSON.

7. Using JSON_CONTAINS() for JSON Search:

   • The JSON_CONTAINS() function allows you to check if a JSON object or array contains a specific value.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE JSON_CONTAINS(employee_details, '"Manager"', '$.job_title');

   • This query returns employees whose job_title in the employee_details JSON is "Manager".

8. Aggregating JSON Data with JSON_ARRAYAGG():

   • You can aggregate JSON data using JSON_ARRAYAGG(), which allows you to group rows into a single JSON array.
   • Example:

   SELECT JSON_ARRAYAGG(first_name) AS employee_names 
   FROM employees;

   • This query aggregates the first_name of all employees into a single JSON array.

9. Handling JSON Arrays:

   • You can extract elements from JSON arrays using JSON_EXTRACT() and array indexes.
   • Example:

   SELECT JSON_UNQUOTE(employee_details->'$.skills[0]') AS primary_skill 
   FROM employees;

   • This query retrieves the first element from the skills array in the employee_details JSON column.

10. Performance Considerations:

• Storing and querying JSON data can affect performance, especially on large datasets. Use appropriate indexing strategies, such as Generated Columns, to improve query performance for frequently accessed JSON fields.

ALTER TABLE employees 
ADD COLUMN first_name_generated VARCHAR(255) AS (JSON_UNQUOTE(employee_details->'$.first_name')) STORED, 
ADD INDEX(first_name_generated);

• This query creates a generated column based on the first_name from the JSON column, improving query performance.

These examples show how you can use MySQL’s JSON functions to store, extract, and manipulate JSON data effectively. Whether you need to handle simple or complex JSON structures, MySQL provides the tools to manage semi-structured data in a relational database environment.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT client_id, first_name, last_name
    complete_address,
    complete_address -> '$.address1',
    complete_address ->> '$.County',
    JSON_UNQUOTE(complete_address -> '$."PO BOX"')
FROM org_client
where complete_address IS NOT NULL;

SELECT client_id, first_name, last_name
    complete_address -> 'address1' AS address1,
    complete_address ->> 'County' AS county
FROM org_client
where complete_address ->> 'PO BOX' = '432';

SELECT client_id, first_name, last_name
    complete_address,
    JSON_VALUE(complete_address, '$.address1') AS address1,
    JSON_VALUE(complete_address, '$.County') AS county
FROM org_client
where JSON_VALUE(complete_address, '$."PO BOX"') = '432';

SELECT client_id,
    first_name AS "info.name",
    last_name AS "info.surname",
    birth_year
FROM org_client
WHERE client_id = 1
FOR JSON PATH;

SELECT JSON DATA

The following SQL queries demonstrate how to extract key-value pairs from a specified JSON column. Additionally, these queries illustrate the utilization of JSON key-value pairs within the WHERE clause.

RAW DATA WITH JSON DATA TYPE

MySQL QUERY

SELECT client_id, first_name, last_name, complete_address, complete_address -> '$.address1', complete_address ->> '$.County', JSON_UNQUOTE(complete_address -> '$."PO BOX"') FROM org_client where complete_address IS NOT NULL;

POSTGRESQL QUERY

SELECT client_id, first_name, last_name,complete_address -> 'address1' AS address1 , complete_address ->> 'County' AS county FROM org_client where complete_address ->> 'PO BOX' = '432';

MS SQL SERVER AND ORACLE QUERY

SELECT client_id, first_name, last_name, complete_address, JSON_VALUE(complete_address, '$.address1') AS address1, JSON_VALUE(complete_address, '$.County') AS county FROM org_client where JSON_VALUE(complete_address, '$."PO BOX"') = '432';

QUERY DATA MAPPING

QUERY OUT PUT