Catalog / PostgreSQL JSON & JSONB Cheatsheet
PostgreSQL JSON & JSONB Cheatsheet
A comprehensive cheat sheet for working with JSON and JSONB data types in PostgreSQL, covering operators, functions, indexing, and performance tips.
JSON/JSONB Operators
Access Operators
|
Access JSON array element (by index) or object field (by key). Examples: |
|
Access JSON array element (by index) or object field (by key) as text. Examples: |
|
Access JSON element at the specified path. Example: |
|
Access JSON element at the specified path as text. Example: |
Containment and Existence Operators
|
Check if key exists within JSON object or element exists within JSON array. Examples: |
|
Check if any of the keys in the text array exist within the JSON object or if any of the elements exist within the JSON array. Example: |
|
Check if all of the keys in the text array exist within the JSON object or if all of the elements exist within the JSON array. Example: |
|
Check if the left JSON contains the right JSON as a sub-object or sub-array (containment). Example: |
|
Check if the right JSON contains the left JSON as a sub-object or sub-array (contained in). Example: |
|
Delete a key (text) from a JSON object or an element (int) from a JSON array. Examples: |
JSON/JSONB Functions
JSONB Creation and Manipulation
|
Converts any SQL value to JSONB. Example: |
|
Builds a JSONB object from a variadic list of key/value pairs. Example: |
|
Builds a JSONB array from a variadic list of values. Example: |
|
Replaces a value inside a JSONB object or array. If Example: |
|
Inserts a new value into a JSONB array. If Example: |
|
Removes all null values from the specified JSONB value. Example: |
JSONB Navigation and Extraction
|
Extracts JSONB value at the given path. Example: |
|
Extracts JSONB value at the given path as text. Example: |
|
Returns a set of keys in the outermost JSONB object. Example: |
|
Returns the number of elements in the JSONB array. Example: |
|
Expands the outermost JSONB array into a set of JSONB elements. Example: |
|
Expands the outermost JSONB object into a set of key-value pairs. Example: |
JSONB Indexing
GIN Indexes
GIN (Generalized Inverted Index) indexes are very useful for indexing JSONB columns, especially when you need to search for keys or values within the JSONB data. Key Points:
|
Creating a GIN index for JSONB:
Creating a GIN index on a specific key:
|
Using GIN index for existence checks:
Using GIN index for containment checks:
|
BRIN Indexes
BRIN (Block Range Index) indexes are suitable when the JSONB data has a natural correlation with the physical order of the table (e.g., time-series data). Key Points:
|
Creating a BRIN index for JSONB:
|
Using BRIN index (effectiveness depends on data order):
|
Expression Indexes
Expression indexes allow you to index specific parts of the JSONB data, improving query performance for specific use cases. Key Points:
|
Creating an expression index on a JSONB key:
Creating an expression index on a function applied to JSONB data:
|
Using expression indexes:
|
JSON Performance Tips
Choosing Between JSON and JSONB
JSON:
JSONB:
|
In most cases, JSONB is the preferred choice due to its superior query performance. Use JSON only when you need to preserve the exact original formatting of the JSON data. |
Query Optimization
Use Indexes:
Avoid Full Table Scans:
Use Specific Operators:
|
Use Prepared Statements:
|
Casting:
|
Data Modeling Considerations
Avoid Storing Large JSON Documents:
Use Consistent Data Types:
|
Normalize Data:
|