Clinical Professor, School of Information
JSON and Natural Language Processing in PostgreSQL
What You'll Learn
- 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.
4 Modules
16 Hours
4 hrs per module (approx.)
Rating
About JSON and Natural Language Processing in PostgreSQL
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.
Skills You'll Gain
- Database Design
- JSON
- Natural Language Processing (NLP)
- NoSQL
- PostgreSQL
- SQL (Programming Language)
What You'll Earn
- Certificate of Completion:
- Certificates of completion acknowledge knowledge acquired upon completion of a non-credit course or program.
Welcome Message
Welcome to JSON and Natural Language Processing in PostgreSQL, a technical course focused on building and using inverted indexes for structured and unstructured data. Learners work with APIs, JSON data, and full-text search tools to support efficient data retrieval within PostgreSQL databases. This is the third course in the PostgreSQL for Everybody Specialization.
This abbreviated syllabus description was created with the help of AI tools and reviewed by staff. The full syllabus is available to those who enroll in the course.
Course Schedule
Module 1: Natural Language
- Video: Welcome to the Course!
- Reading: Help Us Learn More About You
- Reading: Syllabus
- Video: Allocating Rows to Blocks in PostgreSQL
- Video: Index Implementation Details
- Video: Building an Inverted Index with SQL
- Video: Demonstration: SQL Inverse Index
- Video: Building a Natural Language Index with SQL
- Video: Demonstration: SQL Natural Language Index
- Reading: Each Course Uses a Different Database
- Reading: How Assignments Work
- Reading: Connecting to Your Database Server
- App Item: Initial Database Setup
- Video: Bonus: Office Hours - Seoul, South Korea
- Video: Bonus: Office Hours - Portland, Oregon, USA
Module 2: Inverted Indexes with PostgreSQL
- Video: A GIN-based Inverted Index with PostgreSQL
- Video: Demonstration: GIN-based inverted index
- Video: Building a Natural Language Index in PostgreSQL
- Video: Demonstration: Fulltext tsquery and tsvector Functions
- Video: Demonstration: Building a GIN / tsvector Index
- Reading: Assessment Philosophy
- Video: Office Hours - Washington, DC, USA
- Video: Office Hours, Amsterdam, Netherlands
Module 3: Python and PostgreSQL
- Video: Lecture: PostgreSQL and Python
- Video: Demonstration Python and PostgreSQL simple.py
- Video: Demonstration loadbook.py
- Video: Lecture: Mail Archive
- Video: Demonstration: Mail Archive 1/3
- Video: Demonstration: Mail Archive 2/3
- Video: Lecture: Ranking Search Results with PostgreSQL
- Video: Demonstration: Mail Archive 3/3
- Reading: How Python Assignments Work
- Reading: Python and PostgreSQL on PythonAnywhere
- Video: Office Hours: Zagreb, Croatia
- Video: Office Hours, Orlando, Florida, USA
Module 4: JSON and PostgreSQL
- Video: Lecture: JavaScript Object Notation
- Reading: Douglas Crockford and JSON
- Video: Interview: Douglas Crockford - JSON
- Video: Lecture: Python and JSON
- Video: Lecture: PostgreSQL and JSON
- Video: Demonstration: Music Tracks and JSON
- Video: Lecture: Using a JSON API
- Video: Demonstration: Star Wars API 1/2
- Video: Demonstration: Star Wars API 2/2
- Video: Course Wrap Up
- Reading: Post-Course Survey
- Video: Office Hours, London, England
- Video: Office Hours - Montreal, Quebec, Canada (at PyCon)
Grading Policy
Grades are based on multiple programming assignments and quizzes, each weighted between 5%–11.25%, totaling 100% of the course grade.
Intermediate Level
Completion of the first two courses in the Python for Everybody specialization or an equivalent basic understanding of Python
