Dealing with JSON in the relational world by Andres Almiray

JSON can be stored in relational databases in multiple ways:

• As VARCHAR/CLOB (with size limitations)
• As native JSON data type
• As binary/optimized formats (varies by vendor)

Major database vendors have different JSON implementations:

• Oracle: Binary format with version tags, client-side encoding/decoding
• PostgreSQL: JSONB format, table of key-value pairs
• MySQL: Binary format, sorted by key length
• MongoDB: BSON format with streaming support

JSON-Relational duality views allow:

• Exposing relational data as JSON documents
• Working with both document and relational models simultaneously
• Mapping between JSON properties and relational columns
• Evolving from document to relational model as needed

Key considerations for JSON in RDBMS:

• Document size limits vary by vendor (16MB to 1GB+)
• Some formats optimize for read vs write performance
• Schema validation and data type handling differences
• Storage efficiency and compression varies
• Query capabilities and syntax differences

Best practices recommended:

• Run proof-of-concept with real data volumes
• Consider SLA requirements
• Use test containers for development
• Avoid unnecessary document normalization
• Don’t add artificial IDs to JSON documents
• Choose format based on use case needs

Modern databases support multi-model capabilities:

• Single database engine for different data models
• Unified query language across formats
• Combined relational, document, graph, vector support
• REST API access through database tools
• Cloud and container deployment options