JSON Functions

Lesson content

JSON Functions JSON Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly. Example SELECT UPPER(name), ROUND(price, 2), CURRENT_DATE FROM products; Key point Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use. Real-life example A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet. Advanced example BEGIN; -- Preview the exact target set first. SELECT id FROM orders WHERE status = 'pending'; -- Apply the JSON Functions operation, verify affected rows, then commit. COMMIT; Expected result The query returns only the intended rows and columns, with deterministic ordering where order matters. Production check Test with empty, duplicate, null, and boundary values. Use a transaction for related writes. Inspect the execution plan before adding an index. Use parameterized queries for application input. Continue with the PicoStore database This lesson reuses picostore . Relevant tables: customers, products, orders, order_items . Keep the starter rows from the Introduction lesson so results remain comparable. Another practical example SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS average_price, SUM(stock) AS units_available FROM products GROUP BY category HAVING SUM(stock) > 0; Check the result Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct. SQL reference catalog: JSON Functions This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item. Standard/common JSON operations JSON_OBJECT — construct object JSON_ARRAY — construct array JSON_ARRAYAGG — aggregate array JSON_OBJECTAGG — aggregate object JSON_VALUE — extract scalar JSON_QUERY — extract object or array JSON_EXISTS — test path existence JSON_TABLE — turn JSON into relational rows; MySQL/standard support MySQL JSON functions JSON_EXTRACT / -> — extract path ->> — extract unquoted scalar JSON_SET — set or replace path JSON_INSERT — insert missing path JSON_REPLACE — replace existing path JSON_REMOVE — remove path JSON_CONTAINS — test containment JSON_CONTAINS_PATH — test path JSON_KEYS — list object keys JSON_LENGTH — count members JSON_TYPE — report JSON type JSON_VALID — validate JSON text JSON_MERGE_PATCH — merge documents JSON_PRETTY — pretty-print document JSON_SEARCH — find value paths PostgreSQL JSON/JSONB functions and operators -> / ->> — extract JSON or text #> / #>> — extract by path @> — JSONB containment ? — ?| / ?& || — concatenate JSONB jsonb_set — set path jsonb_insert — insert value jsonb_path_query — query SQL/JSON path jsonb_array_elements — expand array to rows jsonb_each — expand object to rows jsonb_object_keys — list keys jsonb_build_object / jsonb_build_array — construct values jsonb_agg / jsonb_object_agg — aggregate JSONB jsonb_typeof — report type jsonb_pretty — pretty-print document to_jsonb — convert SQL value to JSONB jsonb_strip_nulls — remove null object fields Easy example Start with a small customer table and retrieve active customers in a predictable order. SELECT customer_id, name, email FROM customers WHERE status = 'active' ORDER BY name; How to verify the easy example Run it with representative input. Confirm the expected output. Try one missing, invalid, or boundary value. Advanced example Use a CTE and a window function to rank customer revenue while keeping the query readable and testable. WITH customer_revenue AS ( SELECT customer_id, SUM(total_amount) AS revenue FROM orders WHERE order_status = 'completed' GROUP BY customer_id ) SELECT customer_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM customer_revenue ORDER BY revenue_rank, customer_id; Advanced review Explain the tradeoffs and assumptions. Test failure, scale, security, and recovery behavior. Capture evidence from tests, execution plans, logs, or review output. Additional practical guidance PostgreSQL usually favors JSONB and its operators. MySQL has a native JSON type and functions such as JSON_EXTRACT.