SQL Examples
SQL JSON Query
Querying JSON Data in SQL
JSON query with JSON_EXTRACT processes JSON, leveraging PostgreSQL JSONB.
Understanding JSON and JSONB in PostgreSQL
PostgreSQL offers robust support for JSON data types, allowing developers to store and process JSON data efficiently. Among these, JSONB is a binary representation of JSON data that is highly suitable for indexing and efficient querying. Unlike the plain JSON type, JSONB offers better performance for read-heavy operations, making it ideal for applications that require frequent JSON data manipulation.
Using JSON_EXTRACT for JSON Queries
The JSON_EXTRACT function is used to extract data from JSON objects. In PostgreSQL, the equivalent function is ->> for extracting JSONB text values, or -> for extracting JSONB objects. These operators are essential for working with JSON data in SQL queries.
Basic JSON_EXTRACT Usage
In this example, we're extracting the name field from a JSONB column named data in a users table. This query selects users whose role is 'admin'. The ->> operator extracts text from the JSONB field.
Extracting Nested JSON Data
Here, we demonstrate how to extract nested JSON data. We access the city field within an address object in the JSONB data. The -> operator is used to access the nested object, and ->> is used to retrieve the text value of city.
Filtering with JSONB Path Queries
PostgreSQL supports JSON path queries for advanced filtering. You can use the @> operator to check if a JSONB object contains another JSONB object.
This query returns all users where the JSONB data indicates the user is active. The @> operator is powerful for checking the existence of specific keys and values within JSONB columns.
Indexing JSONB Fields for Performance
To enhance query performance, you can create an index on JSONB fields. PostgreSQL provides the gin index, which is particularly effective for JSONB data.
By creating a gin index on the data column, queries involving JSONB operations, such as filtering and extracting data, can execute much faster.
SQL Examples
- Simple Select
- Filtered Query
- Sorted Query
- Joined Query
- Aggregate Report
- Subquery Example
- Correlated Subquery
- Insert Data
- Update Data
- Delete Data
- Create Table
- View Creation
- String Manipulation
- Date Calculations
- JSON Query
- Window Function
- CTE Example
- Pivot Report
- Union Query
- Paginated Query
- Hierarchy Query
- Sales Report
- User Management
- Inventory Query
- Search Query
- Dynamic Filter
- Error Handling
- Data Export
- Data Import
- Table Backup
- Query Logging
- Conditional Aggregation
- Cross Tabulation
- Previous
- Date Calculations
- Next
- Window Function
