SQL
Query and Manipulate JSON Data Stored in SQL Columns
Explore how to extract, query, and even modify JSON data stored directly within database columns using powerful SQL JSON functions.
-- Example using PostgreSQL JSONB functions:
-- Table 'products' with a 'details' column of type JSONB
--
-- 1. Extract a specific value from JSON:
SELECT name, details->>'color' AS product_color
FROM products
WHERE details->>'size' = 'Large';
-- 2. Query based on JSON array elements:
SELECT name
FROM products
WHERE details->'tags' ? 'new_arrival'; -- Checks if 'tags' array contains 'new_arrival'
-- Example using MySQL 5.7+ JSON functions:
-- Table 'users' with a 'preferences' column of type JSON
--
-- 1. Extract a specific value from JSON:
SELECT username, JSON_EXTRACT(preferences, '$.theme') AS user_theme
FROM users
WHERE JSON_EXTRACT(preferences, '$.notifications.email') = TRUE;
-- 2. Update a JSON value:
UPDATE users
SET preferences = JSON_SET(preferences, '$.theme', 'dark')
WHERE id = 123;
How it works: This SQL snippet demonstrates how to interact with JSON data stored in database columns, a common pattern in modern web applications. It provides examples for both PostgreSQL (using `->>` and `?` operators for `JSONB` type) and MySQL (using `JSON_EXTRACT`, `JSON_SET` functions). You can extract specific values, filter rows based on JSON content (including array elements), and even update parts of the JSON document directly within your SQL queries. This allows for flexible schema designs while leveraging the database's querying capabilities.