PostgreSQL Day Chicago - JSON_TABLE() How To (lightning talk)

JSON_TABLE() is in PG 17! This is a quick guide to its usage.

Transcript

1. JSON_TABLE() - HowTo Dave Stokes @Stoker David.Stokes@Percona.com https://speakerdeck.com/stoker 1
2. ©2023 Percona | Confidential Who Am I I am Dave Stokes Technology Evangelist at Percona Author of MySQL & JSON - A Practical Programming Guide Over a decade on the Oracle MySQL Community Team Started with MySQL 3.29 David.Stokes@Percona.com @Stoker https://speakerdeck.com/stoker
3. ©2023 Percona | Confidential JSON_TABLE() - How To JSON_TABLE() Lets you temporarily transform your JSON data into a relational table for processing with SQL commands. It will be in PG 17, but the bad news is that it is very hard to learn from the manual page. So come to this talk! 3
4. JSON_TABLE() Details
5. ©2023 Percona | Confidential An example in, ahem, MySQL 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 | +----------------+----------+ 5 doc - the name of the JSON column “$” is the JSON Path, in this case the entire document columns - what k/v pairs you want • name temporary column • desired casting • path “$.<key>” where to find JSON data In this example $.Name is cast as a CHAR(20) named country_name & $.IdepYear is now an int named IndyYear
6. ©2023 Percona | Confidential It can handle DEFAULT or MISSING values 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) 6 This is a handy way to handle non-existing data that can be common in JSON
7. ©2023 Percona | Confidential Unfortunately, no NESTED PATH in PostgreSQL 17 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 | 7 {"_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"} Hopefully it will be in 18!!!!
8. ©2024 Percona Percona is hiring! • Senior Software Engineer (PostgreSQL) • Support Engineer (PostgreSQL) • PostgreSQL Evangelist … and more!
9. percona.com THANK YOU! David.Stokes@Percona.Com @Stoker speakerdeck.com/stoker