Skip to content

JSON Operations

Jump to bottom
Temp edited this page Oct 3, 2025 · 1 revision

JSON Operations

15 specialized tools for working with JSONB data in PostgreSQL.

📊 Overview

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

🔧 Basic Operations

json_insert

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"]}
)

json_update

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]
)

json_select

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"
)

json_query

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"]}
)

json_merge

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]
)

✅ Validation Tools

json_validate_schema

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": []}

json_validate_path

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"}

json_security_scan

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", "'; --"]
)

🚀 Advanced Tools

jsonb_aggregate

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"
)

jsonb_stats

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
# }

jsonb_diff

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"]
# }

🎯 Common Workflows

JSONB CRUD Operations

# 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]
)

JSON Validation Pipeline

# 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"
)

📚 Related Documentation

See Home for more tool categories.

Clone this wiki locally