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:

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?

  • i understand why the exist, whatr i don't know why people use it, there are so many questions how to get the information, that it makes no sense to use something that you can't control or use. further every company wants ti earhn mponey, so they try to makem their interprettion of sql unique, so that you can't switch
    – nbk
    Aug 12 '20 at 20:29

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.