How do you use JSON data in SQL?

Using JSON data in SQL involves storing, querying, and manipulating JSON-formatted information within relational databases. Most modern SQL databases like PostgreSQL, MySQL, and SQL Server provide built-in functions and operators to handle JSON. For example, you can define a column with a JSON data type (e.g., JSON in PostgreSQL or JSON/JSONB in MySQL) to store structured data like {"name": "Alice", "age": 30}. Once stored, you can extract specific values, filter rows based on JSON content, or modify parts of the JSON structure without rewriting the entire document.

To query JSON data, databases offer functions like JSON_EXTRACT (MySQL) or the -> operator (PostgreSQL). For instance, in PostgreSQL, SELECT data->'name' FROM users WHERE data->>'age' > '25' retrieves the name field from a JSON column data where the age is over 25. You can also update JSON fields using functions like JSON_SET (MySQL) or jsonb_set (PostgreSQL). For example, UPDATE users SET data = jsonb_set(data, '{age}', '31') WHERE id = 1 increments the age value. Additionally, some databases support indexing JSON fields (e.g., using GIN indexes in PostgreSQL) to improve query performance on large datasets.

When working with JSON in SQL, consider scenarios where semi-structured data is unavoidable, such as storing configuration settings or nested attributes. For example, an e-commerce app might store product variants as JSON in a metadata column: {"color": "red", "sizes": ["S", "M"]}. To flatten nested JSON arrays into rows, PostgreSQL’s jsonb_array_elements function can unpack the sizes array. Conversely, you can generate JSON output from relational data using functions like JSON_OBJECT (MySQL) or json_build_object (PostgreSQL). For example, SELECT json_build_object('user_id', id, 'name', username) FROM users creates JSON objects from table rows. Always validate JSON syntax (e.g., PostgreSQL’s IS JSON constraint) to prevent invalid data insertion.