Building JSON from Relational Data
Presented by:
Jeremy Smith
I am an Applications Developer at the Macaulay Library, part of the Cornell Lab of Ornithology. This library houses the world’s premier scientific archive of natural history audio, video, and photographs. I work on databases and APIs for storing metadata about and searching through our collection. I have been an avid user of PostgreSQL for the past 12 years, attempting to straddle the line between database administrator and backend developer.
No video of the event yet, sorry!
JSON is the de facto format for modern APIs and has been part of Postgres since version 9.2. The ability to store, parse, and index JSON documents alongside relational data was game-changing and continuous improvements since 9.2 such as JSONB, indexing support, and the brand new JSON path expressions have made this feature better than ever.
In addition to storing JSON, Postgres provides a number of tools to generate JSON, which have also improved considerably over time. Fluency with these tools can be invaluable for many uses, such as prototyping an API response, avoiding the N+1 query problem, and/or generating multi-level reports.
The functions and operators involved in creating JSON are straightforward, but choosing the correct one and using them effectively can require an advanced grasp of SQL. In this talk, aimed at people with a beginner to intermediate level of SQL proficiency or those who have not explored JSON manipulation in Postgres, we will explore examples and cover the following topics:
- Exploring JSON creation functions
- Choosing the correct functions for use cases
- Aggregating JSON
- Building nested JSON from relational data
- Date:
- Duration:
- 20 min
- Room:
- Conference:
- Postgres Conference 2020
- Language:
- Track:
- Development
- Difficulty:
- Easy