Odoo JSON Fields and PostgreSQL JSONB: Storage and Query Guide

JSON Storage in Odoo

Odoo provides several ways to store structured data as JSON. Each approach has different trade-offs between flexibility, queryability, and type safety.

Serialized Fields (fields.Serialized)

The simplest JSON storage — stores Python objects as JSON text:

from odoo import models, fields

class ConfigModel(models.Model):
    _name = 'my.config'
    _description = 'Configuration'

    settings = fields.Serialized(string='Settings')
    # Stored as JSON text in a TEXT column
    # Can store dicts, lists, strings, numbers, booleans

Usage:

# Write
record.write({
    'settings': {
        'theme': 'dark',
        'notifications': True,
        'max_items': 50,
        'tags': ['urgent', 'review'],
    }
})

# Read
settings = record.settings
theme = settings.get('theme', 'light')
max_items = settings.get('max_items', 20)

Limitations:

• Cannot search or filter by JSON contents via ORM domains
• Stored as TEXT, not native JSONB — no PostgreSQL JSON operators
• No schema validation — any Python object that serializes to JSON

Properties Field (Odoo 17+)

Odoo 17 introduced fields.Properties for user-defined custom attributes:

class ProjectTask(models.Model):
    _inherit = 'project.task'

    custom_properties = fields.Properties(
        string='Custom Properties',
        definition='project_id.custom_properties_definition',
    )

Properties fields provide a UI for defining and editing structured data without custom module development. The definition field on the parent model controls what properties are available.

Raw JSONB Columns

For maximum flexibility and queryability, use PostgreSQL JSONB directly:

class AnalyticsEvent(models.Model):
    _name = 'analytics.event'
    _description = 'Analytics Event'

    event_type = fields.Char(required=True, index=True)
    payload = fields.Text(string='Payload (JSON)')
    # We'll use raw SQL for JSONB operations

    def init(self):
        # Change column type to JSONB for native JSON support
        self.env.cr.execute("""
            DO $$
            BEGIN
                IF EXISTS (
                    SELECT 1 FROM information_schema.columns
                    WHERE table_name = 'analytics_event'
                    AND column_name = 'payload'
                    AND data_type = 'text'
                ) THEN
                    ALTER TABLE analytics_event
                    ALTER COLUMN payload TYPE JSONB
                    USING payload::JSONB;
                END IF;
            END $$;
        """)
        # Add GIN index for JSONB queries
        self.env.cr.execute("""
            CREATE INDEX IF NOT EXISTS
                analytics_event_payload_gin
            ON analytics_event
            USING GIN (payload)
        """)

Querying JSONB Data

With JSONB columns, you can use PostgreSQL JSON operators:

# Query by JSON key value
self.env.cr.execute("""
    SELECT id, payload->>'user_id' AS user_id, payload->>'action' AS action
    FROM analytics_event
    WHERE payload->>'action' = 'purchase'
    AND (payload->>'amount')::numeric > 100
    ORDER BY create_date DESC
    LIMIT 50
""")
results = self.env.cr.dictfetchall()

# Check if key exists
self.env.cr.execute("""
    SELECT COUNT(*)
    FROM analytics_event
    WHERE payload ? 'error_code'
""")

# Nested key access
self.env.cr.execute("""
    SELECT id
    FROM analytics_event
    WHERE payload->'metadata'->>'source' = 'mobile'
""")

# Contains check
self.env.cr.execute("""
    SELECT id
    FROM analytics_event
    WHERE payload @> '{"status": "completed"}'
""")

JSONB Operators Reference

Indexing JSONB

# GIN index — supports @>, ?, ?|, ?& operators
CREATE INDEX idx_payload_gin ON analytics_event USING GIN (payload);

# B-tree index on specific key — supports =, <, >, BETWEEN
CREATE INDEX idx_payload_action ON analytics_event ((payload->>'action'));

# Partial GIN index
CREATE INDEX idx_payload_errors ON analytics_event USING GIN (payload)
WHERE payload ? 'error_code';

JSON vs Relational: When to Use Which

Practical Example: Storing API Responses

class WebhookLog(models.Model):
    _name = 'webhook.log'
    _description = 'Webhook Log'

    endpoint = fields.Char(required=True, index=True)
    method = fields.Char()
    status_code = fields.Integer()
    request_body = fields.Text()  # JSON
    response_body = fields.Text()  # JSON
    headers = fields.Text()  # JSON

    @api.model
    def log_webhook(self, endpoint, method, status, req_body, resp_body, headers):
        return self.create({
            'endpoint': endpoint,
            'method': method,
            'status_code': status,
            'request_body': json.dumps(req_body) if req_body else '',
            'response_body': json.dumps(resp_body) if resp_body else '',
            'headers': json.dumps(dict(headers)) if headers else '',
        })

Analytic Distribution (Odoo's JSONB Usage)

Odoo itself uses JSONB for analytic distribution in accounting:

# Odoo stores analytic distribution as JSONB:
# {"analytic_account_id": percentage}
# Example: {"5": 60.0, "8": 40.0}

analytic_distribution = fields.Json(string='Analytic Distribution')

# The fields.Json type maps directly to PostgreSQL JSONB

fields.Json (Odoo 17+)

Odoo 17 introduced a native fields.Json type that maps to PostgreSQL JSONB:

class MyModel(models.Model):
    _name = 'my.model'

    metadata = fields.Json(string='Metadata')

# Write
record.write({'metadata': {'version': 2, 'tags': ['a', 'b']}})

# Read
version = record.metadata.get('version')

This is the recommended approach for new development — it provides native JSONB storage with ORM integration.

Best Practices

• Use fields.Json (Odoo 17+) for new JSON storage needs
• Use fields.Serialized for backward compatibility with older Odoo versions
• Add GIN indexes on JSONB columns you query frequently
• Do not store relational data in JSON — use proper Many2one/Many2many fields
• Validate JSON structure before writing — JSONB accepts any valid JSON
• Use raw SQL for complex JSONB queries — the ORM does not support JSON operators in domains
• Consider data migration: changing from JSON to relational fields later is painful