JSON Functions
SQL · Extra Concepts
Theory
Many systems store semi-structured data such as API responses, event payloads or app settings as JSON text inside a column. JSON functions let SQL read inside that text: JSON_EXTRACT(payload, '$.city') returns the value of the city key, and paths like '$.address.pin' or '$.items[0]' reach nested objects and array elements. If the path does not exist, the result is NULL rather than an error.
Names differ by database. SQLite and MySQL use JSON_EXTRACT and also the -> and ->> operators (->> returns plain text). PostgreSQL uses -> and ->> on json/jsonb columns. SQL Server uses JSON_VALUE for single values and OPENJSON to expand arrays. Snowflake stores JSON in a VARIANT column and reads it with payload:city, using PARSE_JSON to convert text and FLATTEN to expand arrays. Extracted values are often text, so CAST them before doing maths, and move keys you filter on often into real columns for speed.
Why we use it
Event and API data changes shape often, and forcing it into fixed columns is slow to evolve. JSON functions let you keep the flexible document and still query the fields you need with ordinary SQL.
When to use it
Use JSON functions for logs, events, API payloads and optional attributes. If a key is used in most queries, filters or joins, extract it into a typed column or generated column so it can be indexed and validated.
How it works
- Row with a JSON text column (payload)
- Choose a path: '$.city' or '$.items[0].qty'
- Database parses the JSON and follows the path
- Does the path exist? Yes: Return the value (CAST if numeric). No: Return NULL.
- Use the value in SELECT, WHERE or GROUP BY
Syntax
JSON_EXTRACT(json_column, '$.path')Example
SELECT JSON_EXTRACT('{"name":"Asha","dept":"IT"}', '$.name');
-- 'Asha'Key points
- Lets semi-structured data live alongside relational columns
- Function names vary: JSON_EXTRACT (SQLite/MySQL), -> / ->> operators (Postgres)
- SQL Server uses JSON_VALUE/OPENJSON; Snowflake uses VARIANT with col:key and FLATTEN
- A missing path returns NULL, not an error
- Extract hot keys into real columns so they can be indexed