JSON in SQL was Inevitable (Querying using JSON)

Discussion about why JSON is useful when querying databases and why it is preferable over an ORM.

(If you don’t care about history and are familiar with ORMs you can skip to the Querying using JSON section)

Introduction

The original design of SQL did not predict the needs of querying nested data without aggregation. The initial implementation of SQL (called SEQUEL at the time) was created by IBM in the early 1970s to perform operations on their database management system, System R. This was useful for generating reports but not for querying complex data structures. Once the need for building nested data structures using data from a SQL database was discovered, the immediate question was: how? There have been many attempts at solving this problem, each with pros and cons.

Object Relation Mappers (ORM)

The responsibility of an ORM is to handle mapping projections from/to the database and language-specific data structures. Many “full” ORM frameworks tend to be overkill and attempt to solve problems that don’t exist; I am specifically referring to ORMs that write complex DQL statements for the programmer. This introduces a plethora of issues such as the object-relational impedance mismatch problem, N+1 query problem, and difficulty debugging queries, just to name a few. This pales in comparison to the worst issue caused by these frameworks: programmers not understanding the SQL being run against the database. When a decently sized team uses an ORM, issues will arise where inefficient SQL that grinds production servers to a halt is deployed. We have many reasons to avoid querying via ORM’s, so what is the solution? The solution is simple: continue writing queries but build nested structures inside our SQL.

NOTE: Many modern ORM’s provide mechanisms to avoid the N+1 problem such as eager loading, but this is not the default in many of them. For example Hibernate, a popular Java ORM, defaults to lazy loading one to many and many to many relationships. This kind of behavior is a ticking time bomb for developers that wish to treat SQL as a dumb persistent data layer with key lookups.

There are genuine use cases for ORMs, these frameworks exist for a reason. They are useful for writing DML statements in the vast majority of use cases. They can also allow an application to hook into multiple databases, when making a tool/product this may be a requirement. Lastly they can make simple DQL that deal with one (maybe two) tables less verbose.

Querying using JSON

The official SQL standard has required support for building JSON since 2016 and many popular RDBMS such as SQL Server, PostgreSQL, and MySQL support this. One benefit of building nested data structures in the database is ability to retrieve all required data in a single query. This eliminates the N+1 problem and removes the need for complex query builders that understand relationships between entities. If we take this approach the SQL is no longer hidden from the programmer, making query debugging much easier.

Examples

For these examples I am using PostgreSQL but the same principles apply to any RDBMS that supports JSON. Every example will use a simple system that contains customers, orders, and products as described by the ERD below.

Querying Nested Relationships with JSON

First, we will write a query that retrieves all orders for a customer and include all of the products on those orders. We will use two functions defined in the SQL standard for this: JSON_ARRAYAGG and JSON_OBJECT. We want the data structured such that customer -> orders -> products, so first we will get all customers. For our example, let’s say we are worried about the first 5 customers, but we can filter however we want here.

SELECT name,
    customer_id
FROM customer
LIMIT 5

Next, we want to get all orders for these filtered customers; we will put the customer SELECT in a CTE, as well as our order SELECT. Doing this will allow other CTEs to filter results before performing potentially costly operations such as GROUP BY. This step also uses our JSON functions; in the summary query we will GROUP BY our top-level entities’ primary key, then JSON_ARRAYAGG and build objects out of nested entities. Also notice that we must use the ANY_VALUE function for records that we know are unique given the primary keys GROUP BY. This is because PostgreSQL is unable to determine the functional dependency of the columns from the CTE in relation to the column we are grouping by. After following these steps, we will produce a query that looks like the following:

WITH customer_cte AS (
    SELECT name,
        customer_id
    FROM customer
    LIMIT 5
),
customer_order_cte AS NOT MATERIALIZED(
    SELECT o.order_id,
        o.order_number,
        o.fk_customer_id
    FROM "order" AS o
    INNER JOIN customer_cte AS cc ON cc.customer_id = o.fk_customer_id
)
SELECT cc.customer_id,
    ANY_VALUE(cc."name") AS name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'OrderId': coc.order_id,
            'OrderNumber': coc.order_number
        )
    ) AS orders
    FROM customer_cte AS cc
LEFT JOIN customer_order_cte AS coc ON cc.customer_id = coc.fk_customer_id
GROUP BY cc.customer_id;

Finally, we want all order products and some details about each of those products. Notice the customer_order_cte now includes a GROUP BY on its primary key. It also aggregates the nested order_product_cte using the same method as in the summary query. We will end up with a query that resembles the SQL below:

WITH customer_cte AS (
    SELECT name,
        customer_id
    FROM customer
    LIMIT 5
),
order_product_cte AS NOT MATERIALIZED (
    SELECT order_product_id,
           fk_order_id,
           quantity,
            JSON_OBJECTAGG(
                JSON_OBJECT(
                    'ProductId': p.product_id,
                    'Name': p.name,
                    'BasePriceUsd': p.base_price_usd,
                    'CostToProduceUsd': p.cost_to_produce_usd
                ) AS product_details
            )
    FROM order_product AS op
    LEFT JOIN product AS p ON p.product_id = op.fk_product_id
),
customer_order_cte AS NOT MATERIALIZED(
    SELECT o.order_id,
        o.order_number,
        o.fk_customer_id,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'OrderProductId': opc.order_product_id,
                'Quantity': opc.quantity,
                'Details': opc.product_details
            )
        ) products
    FROM "order" AS o
    LEFT JOIN order_product_cte AS opc ON opc.fk_order_id = o.order_id
    INNER JOIN customer_cte AS cc ON cc.customer_id = o.fk_customer_id
    GROUP BY o.order_id
)
SELECT cc.customer_id,
    ANY_VALUE(cc."name") AS name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'OrderId': coc.order_id,
            'OrderNumber': coc.order_number,
            'Products': coc.products
        )
    ) AS orders
    FROM customer_cte AS cc
LEFT JOIN customer_order_cte AS coc ON cc.customer_id = coc.fk_customer_id
GROUP BY cc.customer_id;

I chose a heavily nested data structure with many duplicated data points. This is a weak point of querying nested data structures; I will talk about ways this can be optimized in the next section. For most real-world use cases, you can stop here.

ORM and JSON SQL Comparison

There are several developers that I have encountered who believe that ORM’s write/execute queries more efficiently than programmers can. This section not only disproves this claim but also provides an example of how easy using custom queries can be.

I used a SQL script to populate test data into the tables described by the ERD. It is not particularly fast, but it gets the job done. All of the tests will run with 10,000 customers, 100,000 orders, 650,438 order products, and 15,000 products. I will also be running each test using -benchtime=3s, which guarantees each test will last a minimum of 3 seconds.

Now that we have test data and a SQL query that fulfills our initial requirements, we will compare it to an ORM. I will be using Golang with GORM. I chose Golang because of its popularity and heavy focus on web programming. Most of the concepts discussed here will broadly apply to any ORM and programming language. First, we will write a simple test using a “full” ORM select; by this I mean populating every field in the entity with values from the DB. This is how I most commonly see ORM’s used, so I believe it is important to test. We will then compare this to our SQL query using a project derived from the query results. The code for all the following testing can be found in the repository here. The tests we are about to run boil down to these snippets (the SQL is embedded from an external file).

func TestOrmFullGorm(db *gorm.DB, customer_num int) []models.Customer {
	var customers []models.Customer

	result := db.
		Preload("Orders.OrderProducts.Product").
		Limit(customer_num).
		Find(&customers)

	if result.Error != nil {
		log.Printf("TestOrm error: %v", result.Error)
		return customers
	}

	return customers
}

func TestJsonGorm(db *gorm.DB, customer_num int) []models.CustomerQueryModel {
	var customers []models.CustomerQueryModel

	err := db.Raw(queries.CustomerSqlJsonQuery, customer_num).Scan(&customers).Error
	if err != nil {
		log.Printf("TestJsonGorm error: %v", err)
		return customers
	}

	return customers
}

Before I started testing, I wrote various functions to collect metrics from the database and application. This will have a small impact on performance however, each test will be affected equally. I should also note I am on Linux with a 13th Gen Intel(R) Core(TM) i5-13600K CPU using go version 1.27.1. For this first test, we will query 25 and 50 customers. Here are the results:

The JSON SQL query used less application memory/CPU, executed faster, and transferred less data over the wire compared to the ORM query. The custom JSON query did use more memory/CPU on the database side, though, which is expected since the database is aggregating our JSON data. This test is not apples-to-apples, though, for the JSON SQL variant we had to spend time writing the query and, more importantly, we are selecting less data. Let’s customize our GORM query to select only what it needs, similar to what our JSON SQL query is doing currently.

func selectivePreload(db *gorm.DB) *gorm.DB {
	return db.
		Select("customer_id, name").
		Preload("Orders", selectOrders).
		Preload("Orders.OrderProducts", selectOrderProducts).
		Preload("Orders.OrderProducts.Product", selectProduct)
}

var selectOrders = func(db *gorm.DB) *gorm.DB {
	return db.Select("order_id, fk_customer_id, order_number")
}

var selectOrderProducts = func(db *gorm.DB) *gorm.DB {
	return db.Select("order_product_id, fk_order_id, fk_product_id, quantity")
}

var selectProduct = func(db *gorm.DB) *gorm.DB {
	return db.Select("product_id, name, base_price_usd, cost_to_produce_usd")
}

func TestOrmSelectiveGorm(db *gorm.DB, customer_num int) []models.Customer {
	var customers []models.Customer

	result := selectivePreload(db).Limit(customer_num).Find(&customers)
	if result.Error != nil {
		log.Printf("TestOrmSelectiveWithoutSizeHint error: %v", result.Error)
	}
	return customers
}

After creating the above set of functions I ran the same test to get 25 and 50 customer and got the following result:

The selective ORM method uses less memory than the full select and executes in less time. More importantly, the selective method uses less total network data than our custom JSON query. This means if our database and API live on different machines, our JSON SQL query may perform worse due to latency. Our custom query is pulling through more data for two reasons: nested products are duplicated across orders, and JSON keys are verbose. If 10 of our customers have the same product on half of their orders and each customer has 100 orders, we have just duplicated 1000 product JSON objects. There are solutions to both of these problems, but they come with trade-offs and should only be implemented if performance is needed.

NOTE: Only consider the next two optimizations after streaming or paging the results has been considered or is already being done. These two strategies alone can significantly reduce an application’s memory usage, increasing throughput. I do not discuss these optimizations further since they can be applied to our ORM and JSON queries for similar performance gains.

Making our JSON GO Fast

Currently, we are dealing with a large and verbose JSON object being sent over the wire; what if we made the keys only a couple of characters? This will make everything from JSON parsing to network transfers faster. I went with a simple strategy of concatenating the first letter of each word for a given property. Our custom SQL query will now look like the following:

WITH customer_cte AS (
    SELECT name,
        customer_id
    FROM customer
    LIMIT $1
),
order_product_cte AS NOT MATERIALIZED (
    SELECT order_product_id,
           fk_order_id,
           quantity,
            JSON_OBJECT(
                'pi': p.product_id,
                'n': p.name,
                'bpu': p.base_price_usd,
                'ctp': p.cost_to_produce_usd
            ) AS product_details
    FROM order_product AS op
    LEFT JOIN product AS p ON p.product_id = op.fk_product_id
),
customer_order_cte AS NOT MATERIALIZED(
    SELECT o.order_id,
        o.order_number,
        o.fk_customer_id,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'opi': opc.order_product_id,
                'q': opc.quantity,
                'd': opc.product_details
            )
        ) products
    FROM "order" AS o
    LEFT JOIN order_product_cte AS opc ON opc.fk_order_id = o.order_id
    INNER JOIN customer_cte AS cc ON cc.customer_id = o.fk_customer_id
    GROUP BY o.order_id
)
SELECT cc.customer_id,
    ANY_VALUE(cc."name") AS name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'oi': coc.order_id,
            'on': coc.order_number,
            'p': coc.products
        )
    ) AS orders
    FROM customer_cte AS cc
LEFT JOIN customer_order_cte AS coc ON cc.customer_id = coc.fk_customer_id
GROUP BY cc.customer_id;

The original query used 38 bytes for the product detail keys and 29 bytes for order product keys. Our new query uses 10 and 5 bytes, respectively, for these same objects. That means we are saving 57 bytes for every product. The keys for the previous order object used 26 bytes, and our updated query uses 5 bytes, saving 21 bytes per order. When querying 25, customers we are returning 236 orders and 1568 products; this means we will be saving 89.3 KB in product keys and 4.9KB in order keys for a total of 94.3KB in savings. We will also need to annotate our go struct with the correct names. This comes with the obvious downsides of producing a difficult-to-read result and needing special data structures or annotations for deserialization.

The second method also requires us to update our SQL a bit; instead of returning one JSON blob, we will return two. First, we will remove the nested product details form the original query and then replace it with a unique map of all the products on our orders. This means the API layer will be responsible for adding each product to our customer order products. The updated SQL will look like the following:

WITH customer_cte AS MATERIALIZED (
    SELECT
        name
        , customer_id
    FROM customer
    ORDER BY customer_id
    LIMIT $1
)

, products_cte AS (
    SELECT
        JSON_OBJECTAGG(
            product_id: JSON_OBJECT(
                'Name': name
                , 'BasePriceUsd': base_price_usd
                , 'CostToProduceUsd': cost_to_produce_usd
            )
        ) AS products
    FROM product AS p
    WHERE EXISTS (
            SELECT 1
            FROM order_product AS op
            INNER JOIN "order" AS o ON op.fk_order_id = o.order_id
            INNER JOIN customer_cte AS cc ON o.fk_customer_id = cc.customer_id
            WHERE op.fk_product_id = p.product_id
        )
)

, order_product_cte AS NOT MATERIALIZED (
    SELECT
        order_product_id
        , fk_order_id
        , quantity
        , fk_product_id
    FROM order_product
)

, customer_order_cte AS NOT MATERIALIZED (
    SELECT
        o.order_id
        , o.order_number
        , o.fk_customer_id
        , JSON_ARRAYAGG(
            JSON_OBJECT(
                'OrderProductId': opc.order_product_id
                , 'ProductId': opc.fk_product_id
                , 'Quantity': opc.quantity
            )
        ) AS products
    FROM "order" AS o
    LEFT JOIN order_product_cte AS opc ON o.order_id = opc.fk_order_id
    INNER JOIN customer_cte AS cc ON o.fk_customer_id = cc.customer_id
    GROUP BY o.order_id
)

, customers_cte AS NOT MATERIALIZED (
    SELECT
        cc.customer_id
        , ANY_VALUE(cc.name) AS name
        , JSON_ARRAYAGG(
            JSON_OBJECT(
                'OrderId': coc.order_id
                , 'OrderNumber': coc.order_number
                , 'Products': coc.products
            )
        ) AS orders
    FROM customer_cte AS cc
    LEFT JOIN customer_order_cte AS coc ON cc.customer_id = coc.fk_customer_id
    GROUP BY cc.customer_id
)

SELECT
    (
        SELECT
            JSON_ARRAYAGG(JSON_OBJECT(
                'CustomerId': customer_id
                , 'Name': name
                , 'Orders': orders
            ))
        FROM customers_cte
    ) AS customers
    , (SELECT products FROM products_cte) AS products;

This will significantly reduce the amount of data sent over the network since we are deduplicating entire JSON objects. I want to note that JSON_OBJECTAGG is ANSI SQL-compliant, since I am using it for the unique product collection aggregate. This method of reducing network data transferred has downsides of needing to write extra logic in the data retrieval function to reconstruct the final result and making the query more complex. The code will now look like the following:

func TestJsonDeDupProductsGorm(db *gorm.DB, customer_num int) models.CustomerDeDupeQueryModelList {
	var queryResult models.CustomerAndProductQueryModelJson

	err := db.Raw(queries.CustomerSqlJsonDeDupeQuery, customer_num).First(&queryResult).Error
	if err != nil {
		log.Printf("TestJsonDeDupProductsGorm error: %v", err)
	}

	for i := range queryResult.Customers {
		for j := range queryResult.Customers[i].Orders {
			products := queryResult.Customers[i].Orders[j].Products
			for k := range products {
				products[k].Details = queryResult.Products[products[k].ProductId]
				products[k].Details.ProductId = products[k].ProductId
			}
		}
	}

	return queryResult.Customers
}

Along with the above optimizations, I also implemented and collected metrics using easyjson, a library that can unmarshal JSON data from our custom queries. The easyjson library does not use reflection; rather, it generates code to marshall/unmarshall JSON before compiling. The Golang ecosystem makes this easy, though, only requiring a single go generate ./... command and some file annotations before compiling to get everything working. I will spare you the details of how this was implemented; it was not difficult, but it is not important for this article.

I have also included metrics for increasing the number of concurrent requests being made. This is important for applications that handle a large number of users or experience high usage spikes.

These results show that the optimizations to our original query win over the ORM query in almost all cases. There are a few cases past 1000 customers where the ORM beats our “JSON Compact” & “Easy JSON Compact” optimizations. This is because using small keys alone does not tackle the root problem, which is duplicating product data across customer aggregations. When dealing with similar situations to this query, I would recommend trying to deduplicate data and creating the final object in the data access function first. If this is not enough, then shrinking the keys or using another library for deserialization are good second options as well.

Conclusion

I have talked a lot about performance in this article, mainly to convey that writing SQL by hand can be performant. That said, I still believe the strongest benefits are readability, debuggability, and transferability compared to a traditional ORM. ORMs get abandoned, programming languages can fade away, but SQL standards endure. This means the SQL you write today is relevant decades later and can persist through shifts in technology stacks.

Notes

The example presented in this article is simple, but real-world problems are rarely like this. For example, what if this query required an estimated price using base_price_usd on each customer order? For our custom query, it would be a singular SUM aggregate, while the GORM query would need to drop into raw SQL for part of the query or do the work in the API layer. For this particular problem, imagine if we no longer required information about each product on an order, instead stopping at the order? If we still want this estimated price aggregate, we are forced to pull in a lot more data than needed or write raw SQL anyways. Debugging a hand-written SQL query is easy; you open the .sql file (or copy the query if you are still hardcoding strings), then execute it with some parameters. In the ORM case, it is not so simple; you are forced to implement logging so you can copy the query if you are lucky. In many cases, the ORM is executing multiple queries; in this situation, you must capture each query and analyze them.

I am aware that some programming languages, such as C# with EF, can convey nested aggregates but, EF comes with its own problems. If we wanted to replicate the queries being executed in the above tests with an estimated cost aggregate, we would need something like this:

var customers = await dbContext.Customers
    .Take(customerNum)
    .Select(c => new 
    {
        CustomerId = c.CustomerId,
        Name = c.Name,
        Orders = c.Orders.Select(o => new
        {
            OrderId = o.OrderId,
            FkCustomerId = o.FkCustomerId,
            OrderNumber = o.OrderNumber,
            EstimatedCost = o.OrderProducts.Sum(op => op.Quantity * op.Product.BasePriceUsd),
            OrderProducts = o.OrderProducts.Select(op => new
            {
                OrderProductId = op.OrderProductId,
                FkOrderId = op.FkOrderId,
                FkProductId = op.FkProductId,
                Quantity = op.Quantity,
                Product = new
                {
                    ProductId = op.Product.ProductId,
                    Name = op.Product.Name,
                    BasePriceUsd = op.Product.BasePriceUsd,
                    CostToProduceUsd = op.Product.CostToProduceUsd
                }
            }).ToList()
        }).ToList()
    })
    .ToListAsync();

This query does the same thing as our GORM query (plus the estimated price aggregate) but is a lot more verbose. This is a far-cry from the simplicity promised by using an ORM.

References

• https://en.wikipedia.org/wiki/SQL
• https://en.wikipedia.org/wiki/Relational_algebra
• https://en.wikipedia.org/wiki/Tuple_relational_calculus
• https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping
• https://en.wikipedia.org/wiki/Object%E2%80%93relational_impedance_mismatch
• https://www.explainthis.io/en/swe/n-plus-one-problem
• https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver17
• https://en.wikipedia.org/wiki/MySQL
• https://en.wikipedia.org/wiki/PostgreSQL
• https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
• https://github.com/go-gorm/gorm
• https://github.com/mailru/easyjson
• https://github.com/bytedance/sonic
• https://github.com/goccy/go-json
• https://en.wikipedia.org/wiki/Data_query_language
• https://www.evanjones.ca/network-latencies-2021.html
• https://www.postgresql.org/docs/current/sql-altertable.html