Merging JSON Documents in MySQL

Merge two JSON Documents Using JSON_MERGE_PRESERVE

Write a MySQL query to merge two JSON documents, preserving duplicate keys, using the JSON_MERGE_PRESERVE function.

Solution:

-- Merge two JSON documents representing partial product details.

-- Use JSON_MERGE_PRESERVE to combine two JSON objects into one.
-- JSON_MERGE_PRESERVE preserves all fields, including duplicates, and combines arrays.
SELECT JSON_MERGE_PRESERVE(
    -- First JSON object containing brand and model details.
    '{"brand": "TechCorp", "model": "X100"}',
    
    -- Second JSON object containing color and warranty details.
    '{"color": "black", "warranty": "2 years"}'
) AS MergedJSON;  -- Alias the result as MergedJSON for clarity. 

Explanation:

• Purpose of the Query:
• To combine two JSON documents into a single JSON object.
• Demonstrates how to merge semi-structured data while preserving duplicate keys if any.
• Key Components:
• JSON_MERGE_PRESERVE(...) : Merges the provided JSON documents.
• AS MergedJSON : Assigns a name to the resulting JSON object.
• Real-World Application:
• Useful when consolidating data from multiple sources or updates into a single JSON structure.

Notes:

• JSON_MERGE_PRESERVE maintains both values for duplicate keys as arrays.
• This function is valuable for integrating complex JSON data without loss of information.

For more Practice: Solve these Related Problems:

• Write a MySQL query to merge two JSON documents that contain overlapping keys and verify the resulting structure.
• Write a MySQL query to merge three JSON documents into one using JSON_MERGE_PRESERVE.
• Write a MySQL query to merge two JSON documents ensuring that duplicate keys are preserved as arrays.
• Write a MySQL query to merge JSON documents that include nested objects, maintaining all levels of hierarchy.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Extract Nested JSON Value.
Next MySQL Exercise: Create a Generated Column from JSON Data.