PostgreSQL 17’s JSON_TABLE isn’t Particularly Powerful

Posted on In Views:

Exploring practical differences between JSONB and JSON_TABLE in PostgreSQL 17

Recently Postgres 17 was released, and one of the features interesting to me was the JSON_TABLE keyword. Postgres has always had an ambition to dominate the database market, and of course that includes document databases.

After the release of JSONB support in previous versions of Postgres, this time JSON structures have been made more descriptive. I was expecting a lot of groundbreaking, but JSON_TABLE looks a bit “ordinary”. In my opinion, JSON_TABLE just provides syntactic sugar, not much difference in usage.

Let’s take a quick look at some examples to get a feel for the difference between JSON_TABLE and the JSONB of the past.

Preparation

Let’s start by creating a table. In order to be as close to the document database as possible, we’ll use a single JSONB column to store all the order data, including the order item and the customer.

1
2
3
4
CREATE TABLE orders (  
  id serial PRIMARY KEY,  
  order_data JSONB  
);

Then we put in two orders, each with two items.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO orders (order_data) VALUES  
('{  
  "items": [  
    { "name": "Laptop", "price": 1200 },  
    { "name": "Mouse", "price": 25 }  
  ],  
  "customer": { "id": 123, "name": "John Doe" }  
}'),  
('{  
  "items": [  
    { "name": "Keyboard", "price": 100 },  
    { "name": "Monitor", "price": 300 }  
  ],  
  "customer": { "id": 456, "name": "Jane Smith" }  
}');

Okay, let’s experience JSON_TABLE.

JSON_TABLE vs. JSONB

In the past, when we wanted to query for orders with Laptop we would manipulate items through jsonb_array_elements.

1
2
3
4
5
6
7
8
9
SELECT  
  item->>'name' AS item_name,  
  item->>'price' AS item_price,  
  order_data -- whole order including Laptop  
FROM  
  orders,  
  jsonb_array_elements(order_data->'items') AS item  
WHERE  
  item->>'name' = 'Laptop';

So what would this query look like with JSON_TABLE? Let’s see the following example.

1
2
3
4
5
6
7
8
9
10
11
SELECT  
  jt.item_name,  
  jt.item_price,  
  order_data  
FROM orders o,  
  json_table(o.order_data, '$.items[*]'  
  COLUMNS (  
    item_name TEXT PATH '$.name',  
    item_price NUMERIC PATH '$.price'  
  )) AS jt  
WHERE jt.item_name = 'Laptop';

I have to say the difference is not obvious, but that’s partly because string matching is pretty simple. If we want to look up the price of an item, we’ll start to see the advantages of JSON_TABLE.

Suppose we want to find an order with an item price less than 100, we use the original JSONB as follows.

1
2
3
4
5
6
7
8
9
SELECT  
  item->>'name' AS item_name,  
  item->>'price' AS item_price,  
  order_data  
FROM  
  orders,  
  jsonb_array_elements(order_data->'items') AS item  
WHERE  
  (item->>'price')::numeric < 100;

We have to add explicit type casts so that we can compare numbers. But in JSON_TABLE query, since there are already defined type casts, we can compare directly.

1
2
3
4
5
6
7
8
9
10
11
SELECT  
  jt.item_name,  
  jt.item_price,  
  order_data  
FROM orders o,  
  json_table(o.order_data, '$.items[*]'  
  COLUMNS (  
    item_name TEXT PATH '$.name',  
    item_price NUMERIC PATH '$.price'  
  )) AS jt  
WHERE jt.item_price < 100;

Nested JSON

Now we’ve familiarized ourselves with general JSON manipulation, let’s look at the benefits of nested JSON in a JSON_TABLE.

Let’s start by inserting a few test data.

1
2
3
4
INSERT INTO orders (order_data) VALUES  
('{"order": {"items": [{"name": "Laptop", "price": 1200, "details": {"warranty": "2 years", "color": "gray"}}, {"name": "Mouse", "price": 25, "details": {"warranty": "1 year", "color": "black"}}], "customer": {"id": 123, "name": "John Doe"}}}'),  
('{"order": {"items": [{"name": "Keyboard", "price": 70, "details": {"warranty": "1 year", "color": "blue"}}, {"name": "Monitor", "price": 300, "details": {"warranty": "3 years", "color": "black"}}], "customer": {"id": 456, "name": "Jane Smith"}}}'),  
('{"order": {"items": [{"name": "Tablet", "price": 400, "details": {"warranty": "2 years", "color": "white"}}], "customer": {"id": 789, "name": "Alice Johnson"}}}');

There is now an additional details attribute for each order item, which contains the color and warranty.

I would like to check the items that are blue in color, and I can do this with JSONB.

1
2
3
4
5
6
7
8
9
SELECT  
  item->>'name' AS item_name,  
  item->>'price' AS item_price,  
  item->'details'->>'warranty' AS warranty  
FROM  
  orders,  
  jsonb_array_elements(order_data->'order'->'items') AS item  
WHERE  
  item->'details'->>'color' = 'blue';

As we can see from the WHERE clause above, our chain is starting to get longer. But in the JSON_TABLE query we can keep the WHERE simple.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  
  jt.item_name,  
  jt.item_price,  
  jt.warranty  
FROM orders o,  
  json_table(o.order_data, '$.order.items[*]'  
  COLUMNS (  
    item_name TEXT PATH '$.name',  
    item_price NUMERIC PATH '$.price',  
    warranty TEXT PATH '$.details.warranty',  
    color TEXT PATH '$.details.color'  
  )) AS jt  
WHERE jt.color = 'blue';

Conclusion

In a simple JSON query, JSON_TABLE really doesn’t have any advantage at all. But when the query gets complex, the fact that SELECT and WHERE can still be kept simple under the JSON_TABLE structure is a big advantage.

Personally, I’m happy to see Postgres continue to improve its document database features, which will probably force other SQL based databases to start following along.

Overall, JSON_TABLE is a step forward, and there are many more possibilities for structuring unstructured data in the future.

Originally published on Medium