JSON Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
SELECT UPPER(name), ROUND(price, 2), CURRENT_DATE FROM products;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the JSON Functions operation, verify affected rows, then commit.\nCOMMIT;The query returns only the intended rows and columns, with deterministic ordering where order matters.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT category,\n COUNT(*) AS products,\n ROUND(AVG(price), 2) AS average_price,\n SUM(stock) AS units_available\nFROM products\nGROUP BY category\nHAVING SUM(stock) > 0;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
JSON_OBJECT — construct objectJSON_ARRAY — construct arrayJSON_ARRAYAGG — aggregate arrayJSON_OBJECTAGG — aggregate objectJSON_VALUE — extract scalarJSON_QUERY — extract object or arrayJSON_EXISTS — test path existenceJSON_TABLE — turn JSON into relational rows; MySQL/standard supportJSON_EXTRACT / -> — extract path->> — extract unquoted scalarJSON_SET — set or replace pathJSON_INSERT — insert missing pathJSON_REPLACE — replace existing pathJSON_REMOVE — remove pathJSON_CONTAINS — test containmentJSON_CONTAINS_PATH — test pathJSON_KEYS — list object keysJSON_LENGTH — count membersJSON_TYPE — report JSON typeJSON_VALID — validate JSON textJSON_MERGE_PATCH — merge documentsJSON_PRETTY — pretty-print documentJSON_SEARCH — find value paths-> / ->> — extract JSON or text#> / #>> — extract by path@> — JSONB containment? — ?| / ?&|| — concatenate JSONBjsonb_set — set pathjsonb_insert — insert valuejsonb_path_query — query SQL/JSON pathjsonb_array_elements — expand array to rowsjsonb_each — expand object to rowsjsonb_object_keys — list keysjsonb_build_object / jsonb_build_array — construct valuesjsonb_agg / jsonb_object_agg — aggregate JSONBjsonb_typeof — report typejsonb_pretty — pretty-print documentto_jsonb — convert SQL value to JSONBjsonb_strip_nulls — remove null object fieldsStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;JSON Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
SELECT UPPER(name), ROUND(price, 2), CURRENT_DATE FROM products;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the JSON Functions operation, verify affected rows, then commit.\nCOMMIT;The query returns only the intended rows and columns, with deterministic ordering where order matters.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT category,\n COUNT(*) AS products,\n ROUND(AVG(price), 2) AS average_price,\n SUM(stock) AS units_available\nFROM products\nGROUP BY category\nHAVING SUM(stock) > 0;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
JSON_OBJECT — construct objectJSON_ARRAY — construct arrayJSON_ARRAYAGG — aggregate arrayJSON_OBJECTAGG — aggregate objectJSON_VALUE — extract scalarJSON_QUERY — extract object or arrayJSON_EXISTS — test path existenceJSON_TABLE — turn JSON into relational rows; MySQL/standard supportJSON_EXTRACT / -> — extract path->> — extract unquoted scalarJSON_SET — set or replace pathJSON_INSERT — insert missing pathJSON_REPLACE — replace existing pathJSON_REMOVE — remove pathJSON_CONTAINS — test containmentJSON_CONTAINS_PATH — test pathJSON_KEYS — list object keysJSON_LENGTH — count membersJSON_TYPE — report JSON typeJSON_VALID — validate JSON textJSON_MERGE_PATCH — merge documentsJSON_PRETTY — pretty-print documentJSON_SEARCH — find value paths-> / ->> — extract JSON or text#> / #>> — extract by path@> — JSONB containment? — ?| / ?&|| — concatenate JSONBjsonb_set — set pathjsonb_insert — insert valuejsonb_path_query — query SQL/JSON pathjsonb_array_elements — expand array to rowsjsonb_each — expand object to rowsjsonb_object_keys — list keysjsonb_build_object / jsonb_build_array — construct valuesjsonb_agg / jsonb_object_agg — aggregate JSONBjsonb_typeof — report typejsonb_pretty — pretty-print documentto_jsonb — convert SQL value to JSONBjsonb_strip_nulls — remove null object fieldsStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;