JSON and Natural Language Processing in PostgreSQL

Course Provided by: University of Michigan
Course Taken on: Coursera
(4.7) 0
Save Course Go to class


Within this course, you’ll learn about how PostgreSQL creates and uses inverted indexes for JSON and natural language content. We will use various sources of data for our databases, including access to an online API and spidering its data and storing the data in a JSON column in PostgreSQL. Students will explore how full-text inverted indexes are structured. Students will build their own inverted indexes and then make use of PostgreSQL built-in capabilities to support full-text indexes.


  • Compare Python, PostgreSQL, and JSON.
  • Index and retrieve natural language text and JSON data; access API data and store it in a database.
  • Create a GIN-based text[] reverse index and ts_vector index; build a search engine in PostgreSQL.


  • Natural Language
  • Welcome to the Course!
  • Allocating Rows to Blocks in PostgreSQL
  • Index Implementation Details
  • Building an Inverted Index with SQL
  • Demonstration: SQL Inverse Index
  • Building a Natural Language Index with SQL
  • Demonstration: SQL Natural Language Index
  • Bonus: Office Hours - Seoul, South Korea
  • Bonus: Office Hours - Portland, Oregon, USA
  • Help Us Learn More About You
  • Each Course Uses a Different Database
  • How Assignments Work
  • Connecting to Your Database Server
  • Inverted Indexes with PostgreSQL
  • A GIN-based Inverted Index with PostgreSQL
  • Demonstration: GIN-based inverted index
  • Building a Natural Language Index in PostgreSQL
  • Demonstration: Fulltext tsquery and tsvector Functions
  • Demonstration: Building a GIN / tsvector Index
  • Office Hours - Washington, DC, USA
  • Office Hours, Amsterdam, Netherlands
  • Assessment Philosophy
  • Text In Databases
  • Python and PostgreSQL
  • Lecture: PostgreSQL and Python
  • Demonstration Python and PostgreSQL simple.py
  • Demonstration loadbook.py
  • Lecture: Mail Archive
  • Demonstration: Mail Archive 1/3
  • Demonstration: Mail Archive 2/3
  • Lecture: Ranking Search Results with PostgreSQL
  • Demonstration: Mail Archive 3/3
  • Office Hours: Zagreb, Croatia
  • Office Hours, Orlando, Florida, USA
  • How Python Assignments Work
  • Python and PostgreSQL on PythonAnywhere
  • JSON and PostgreSQL
  • Lecture: JavaScript Object Notation
  • Interview: Douglas Crockford - JSON
  • Lecture: Python and JSON
  • Lecture: PostgreSQL and JSON
  • Demonstration: Music Tracks and JSONB
  • Lecture: Using a JSON API
  • Demonstration: Star Wars API 1/2
  • Demonstration: Star Wars API 2/2
  • Course Wrap Up
  • Office Hours, London, England
  • Office Hours - Montreal, Quebec, Canada (at PyCon)
  • Douglas Crockford and JSON
  • Post-Course Survey
  • JSON and PostgreSQL
  • English
  • Charles Russell Severance
  • Coursera