JSON vs Record/Struct type in a DB

Question

It seems like some databases have implement a Record/Struct type and others have implemented a Json/Jsonb type to handle nested data structures. A few examples being:

• BigQuery Struct
• CockroachDB Jsonb
• MySQL8+ Json
• Postgres Json and Postgres 'Composite'

Why do some databases use a json field to support a complex type and others use a struct field? For example, if the json field is essentially a string field with many helper methods (to validate, to extract a value, etc.), why don't those databases also incorporate a struct type? Is the struct type much more difficult and complex to implement, or what are the differences in why some dbs have it and others do not?

Answer 1

The answer goes way back to relational theory, and the idea that column values should always be scalars. Arrays, structs, and nested result sets can alternatively be modeled as separate tables, and normally should be. It's baked right in to the First Normal Form.

It turns out that this is not really a great rule, especially for query results, where having arrays, records, or nested results is quite useful. IE it's good to enforce for a table design, but restrictive to prohibit in a query result. It would often be very handy to write

select c.CustomerId, 
       Orders = (select * from Orders where CustomerId = c.CustomerID)
from Customers c

And have that return one row per customer with a nested result of the customer's orders.

Anyway in such systems (and I'm thinking of SQL Server here) XML, and later JSON were added not primarily to add structured types to the database type system, but to

1. Support semi-structured and flexible schema designs
2. Interoperate with external systems that use XML or JSON

The fact that they allow you create tables with non-scalar columns was a side-effect.