Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PostgreSQL Day Chicago - JSON_TABLE() How To (l...

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

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

David Stokes

April 26, 2024
Tweet

More Decks by David Stokes

Other Decks in Technology

Transcript

  1. ©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 [email protected] @Stoker https://speakerdeck.com/stoker
  2. ©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
  3. ©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
  4. ©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
  5. ©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!!!!
  6. ©2024 Percona Percona is hiring! • Senior Software Engineer (PostgreSQL)

    • Support Engineer (PostgreSQL) • PostgreSQL Evangelist … and more!