-
Notifications
You must be signed in to change notification settings - Fork 0
JSON Operations
Temp edited this page Oct 3, 2025
·
1 revision
15 specialized tools for working with JSONB data in PostgreSQL.
PostgreSQL's JSONB data type provides powerful JSON storage and querying capabilities. These tools make it easy to work with JSON data.
| Category | Tools | Purpose |
|---|---|---|
| Basic Operations | 5 | Insert, update, select, query, merge |
| Validation | 3 | Schema validation, path validation, security |
| Advanced | 7 | Aggregation, statistics, diff, transformation |
Insert JSON data into a JSONB column.
Parameters:
-
table_name(string, required): Target table -
json_column(string, required): JSONB column name -
json_data(object/string, required): JSON data to insert
Example:
json_insert( table_name="users", json_column="profile", json_data={"name": "Alice", "age": 30, "tags": ["admin", "user"]} )
Update JSON data in existing rows.
Parameters:
-
table_name(string, required): Target table -
json_column(string, required): JSONB column name -
json_data(object/string, required): New JSON data -
where_clause(string, optional): SQL WHERE condition -
where_params(list, optional): Parameters for WHERE clause
Example:
json_update( table_name="users", json_column="profile", json_data={"age": 31}, where_clause="id = %s", where_params=[123] )
Select and filter JSON data.
Parameters:
-
table_name(string, required): Source table -
json_column(string, required): JSONB column name -
path_expression(string, optional): JSON path (e.g.,$.user.name) -
where_clause(string, optional): SQL WHERE condition -
limit(integer, optional): Result limit
Example:
# Get all profiles json_select( table_name="users", json_column="profile" ) # Extract specific path json_select( table_name="users", json_column="profile", path_expression="$.address.city" )
Advanced JSON querying with operators.
Parameters:
-
table_name(string, required): Source table -
json_column(string, required): JSONB column name -
query_conditions(object, required): JSON query conditions -
limit(integer, optional): Result limit
Example:
# Find users in a specific city json_query( table_name="users", json_column="profile", query_conditions={"address": {"city": "New York"}}, limit=10 ) # Array containment json_query( table_name="users", json_column="profile", query_conditions={"tags": ["admin"]} )
Merge JSON objects (deep merge).
Parameters:
-
table_name(string, required): Target table -
json_column(string, required): JSONB column name -
merge_data(object, required): Data to merge -
where_clause(string, optional): SQL WHERE condition -
where_params(list, optional): Parameters for WHERE clause
Example:
# Add new fields without replacing existing json_merge( table_name="users", json_column="profile", merge_data={"verified": True, "last_login": "2025-10-03"}, where_clause="id = %s", where_params=[123] )
Validate JSON against a JSON Schema.
Parameters:
-
json_data(string, required): JSON to validate -
json_schema(string, required): JSON Schema definition
Returns:
-
is_valid(boolean): Validation result -
errors(array): Validation errors if any
Example:
schema = { "type": "object", "properties": { "name": {"type": "string"}, "age": {"type": "integer", "minimum": 0} }, "required": ["name"] } result = json_validate_schema( json_data='{"name": "Alice", "age": 30}', json_schema=json.dumps(schema) ) # Returns: {"is_valid": True, "errors": []}
Validate that a JSON path exists and has expected type.
Parameters:
-
json_data(string, required): JSON to validate -
path_expression(string, required): JSON path (e.g.,$.user.name) -
expected_type(string, optional): Expected type (string,number,boolean,object,array)
Example:
result = json_validate_path( json_data='{"user": {"name": "Alice", "age": 30}}', path_expression="$.user.name", expected_type="string" ) # Returns: {"is_valid": True, "value": "Alice", "actual_type": "string"}
Scan JSON for potential security issues.
Parameters:
-
table_name(string, required): Table to scan -
json_column(string, required): JSONB column name -
check_patterns(list, optional): Custom patterns to check
Returns:
- Security warnings (SQL injection patterns, XSS, etc.)
- Suspicious data patterns
- Recommendations
Example:
result = json_security_scan( table_name="users", json_column="profile", check_patterns=["<script", "DROP TABLE", "'; --"] )
Aggregate JSON data with grouping.
Parameters:
-
table_name(string, required): Source table -
json_column(string, required): JSONB column name -
aggregate_function(string, required):array_agg,object_agg,count -
group_by(string, optional): Column to group by
Example:
# Collect all profiles into array jsonb_aggregate( table_name="users", json_column="profile", aggregate_function="array_agg" ) # Group by department jsonb_aggregate( table_name="users", json_column="profile", aggregate_function="array_agg", group_by="department_id" )
Calculate statistics on JSON fields.
Parameters:
-
table_name(string, required): Source table -
json_column(string, required): JSONB column name -
path_expression(string, optional): JSON path for specific field
Returns:
- Key frequency counts
- Value type distribution
- Depth statistics
- Size metrics
Example:
result = jsonb_stats( table_name="users", json_column="profile" ) # Returns: { # "total_rows": 1000, # "most_common_keys": ["name", "age", "email"], # "avg_key_count": 5.2, # "max_depth": 3, # "avg_size_bytes": 248 # }
Compare two JSON objects and show differences.
Parameters:
-
json_a(string, required): First JSON -
json_b(string, required): Second JSON
Returns:
- Added keys
- Removed keys
- Changed values
- Unchanged keys
Example:
result = jsonb_diff( json_a='{"name": "Alice", "age": 30}', json_b='{"name": "Alice", "age": 31, "city": "NYC"}' ) # Returns: { # "added": ["city"], # "removed": [], # "changed": ["age"], # "unchanged": ["name"] # }
# 1. Insert json_insert( table_name="users", json_column="profile", json_data={"name": "Alice", "email": "alice@example.com"} ) # 2. Read data = json_select( table_name="users", json_column="profile", where_clause="id = %s", where_params=[1] ) # 3. Update json_update( table_name="users", json_column="profile", json_data={"email": "newemail@example.com"}, where_clause="id = %s", where_params=[1] ) # 4. Merge json_merge( table_name="users", json_column="profile", merge_data={"verified": True}, where_clause="id = %s", where_params=[1] )
# 1. Define schema schema = { "type": "object", "properties": { "name": {"type": "string"}, "age": {"type": "integer", "minimum": 0} }, "required": ["name"] } # 2. Validate data validation = json_validate_schema( json_data='{"name": "Alice", "age": 30}', json_schema=json.dumps(schema) ) # 3. Check specific paths path_check = json_validate_path( json_data='{"name": "Alice", "age": 30}', path_expression="$.age", expected_type="number" ) # 4. Security scan security = json_security_scan( table_name="users", json_column="profile" )
- Core Database Tools - Basic database operations
- Text Processing - Text search in JSON
- Security Best Practices - Secure JSON handling
See Home for more tool categories.