JSON_TABLE() will be in PostgreSQL 17! It almost made it two years ago but was yanked away at the last minute. We will get it shortly when PostgreSQL 17 is officially released this year! Please see this.
Why is this important?
JSON has become the data interchange format of choice for most developers. JSON had a litany of features that will not be repeated here. But dealing with JSON in a relational database can be, at best, tricky.
Keeping incoming data in JSON has a lot of merit but you end up dealing with JSON handling functions that can be less than intuitive. Or you can extract the data into a relational column for high-speed access, the power of SQL, and the ability to index but the data is no longer in JSON format. Sometimes, you have the data in two places at the same time, incurring the misery of keeping both current.
But what if temporarily that JSON data was converted into a relational table? Your original data is still in JSON format. You can use the power of SQL commands with this temporarily structured data.
You can with JSON_TABLE()!
JSON_TABLE()
MySQL added JSON_TABLE() several years ago, and it is popular. Oracle and SQL Server also have it. And soon, so will PostgreSQL.
You need to tell JSON_TABLE() the name of the JSON document, the document path, and how you want to cast the JSON values. The following example shows the use of JSON_TABLE() with MySQL. Expect PostgreSQL 17 examples when that version is released.
The first example shows how the JSON value stored under the key of ‘Name’ is cast as a char(20) and now called country_name. And how the IndepYear key/value becomes an integer known as IndyYear. The output is passed to Structured Query Language for processing. The SQL here is simple but it could be Window Functions oranalytics.
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> select country_name, IndyYear from countryinfo, json_table(doc,"$" columns (country_name char(20) path "$.Name", IndyYear int path "$.IndepYear")) as stuff where IndyYear > 1992; +----------------+----------+ | country_name | IndyYear | +----------------+----------+ | Czech Republic | 1993 | | Eritrea | 1993 | | Palau | 1994 | | Slovakia | 1993 | +----------------+----------+ 4 rows in set, 67 warnings (0.00 sec) |
Missing data can be easily dealt with, as seen below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> SELECT name, Info->>"$.Population", Pop FROM city2, JSON_TABLE(Info,"$" COLUMNS ( Pop INT PATH "$.Population" DEFAULT '999' ON ERROR DEFAULT '987' ON EMPTY)) AS x1; +-------+-----------------------+------+ | name | Info->>"$.Population" | Pop | +-------+-----------------------+------+ | alpha | 100 | 100 | | beta | fish | 999 | | delta | 15 | 15 | | gamma | NULL | 987 | +-------+-----------------------+------+ 4 rows in set, 1 warning (0.00 sec) |
Sadly, the JSON_TABLE() in PostgreSQL will be coming without the ability to handle nested columns. That becomes handy when provided with data like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 | {"_id": "00005b2176ae0000000000000001", "name": "Morris Park Bake Shop", "grades": [ {"date": {"$date": 1393804800000}, "grade": "A", "score": 2}, {"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, {"date": {"$date": 1358985600000}, "grade": "A", "score": 10}, {"date": {"$date": 1322006400000}, "grade": "A", "score": 9}, {"date": {"$date": 1299715200000}, "grade": "B", "score": 14}], "address": {"coord": [-73.856077, 40.848447], "street": "Morris Park Ave", "zipcode": "10462", " "cuisine": "Bakery", "restaurant_id": "30075445"} |
The NESTED PATH operator allows access to each of the grades.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> select aaaa.* from restaurants, json_table(doc, "$" COLUMNS (name char(50) path "$.name", style varchar(50) path "$.cuisine", NESTED PATH '$.grades[*]' COLUMNS (Grading char(10) path "$.grade", Score INT path "$.score"))) as aaaa ; +--------------------------------+------------+---------+-------+ | name | style | Grading | Score | +--------------------------------+------------+---------+-------+ | Morris Park Bake Shop | Bakery | A | 2 | | Morris Park Bake Shop | Bakery | A | 6 | | Morris Park Bake Shop | Bakery | A | 10 | | Morris Park Bake Shop | Bakery | A | 9 | | Morris Park Bake Shop | Bakery | B | 14 | | Wendy'S | Hamburgers | A | 8 | | Wendy'S | Hamburgers | B | 23 | | Wendy'S | Hamburgers | A | 12 | | Wendy'S | Hamburgers | A | 12 | | Dj Reynolds Pub And Restaurant | Irish | A | 2 | +--------------------------------+------------+---------+-------+ 10 rows in set (0.00 sec) |
Conclusion
JSON_TABLE() is a great addition to PostgreSQL 17. Those of us who deal with lots of JSON-formatted data will make heavy use of it. Hopefully, in the future, this new addition will evolve, and things like JSON schema validation can be included too.
This is great news. Thank you to all the contributors involved.
