JSON and Natural Language Processing in PostgreSQL

U-M Free Access Enrollment Options

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

What You'll Earn

Certificate of Completion: Certificates of completion acknowledge knowledge acquired upon completion of a non-credit course or program.

Experience Type: 100% Online
Format: Self-Paced
Series: PostgreSQL for Everybody
Subject: Computer Science

Data Science
Platform: Coursera, edX

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.

Charles Severance

Clinical Professor, School of Information

Course content developed by U-M faculty and managed by the university. Faculty titles and affiliations are updated periodically.

Intermediate Level

Completion of the first two courses in the Python for Everybody specialization or an equivalent basic understanding of Python

Individuals

This experience is available to individual learners on the following platforms:

U-M Community

Free access is only available to current U-M students, alumni, faculty, and staff.

Organizations

Special pricing and tailored programming bundles available for organizational partners.

4.6

115 Ratings from Coursera

Most Recent Reviews

Read all reviews

December 27, 2024

Demo videos' audio hisses all the time. The lecturer describes the commit method as if it was just flushing. The code examples/templates are intern-level: just ugly code-style-wise (PEP8 weeps tears of disapointment whenever it glances at these), undocumented (the lecturer even removes some instructions from the code, because does not remember why it had to be put there xD, despite boasting about this code having been polished "for two weeks"), or full of poor logic, and I cannot understand how the lecturer can boast so much about calling a `close` method at the end of the entire script while there is no try-catch-finally block that could ensure that the resource would be closed properly on errors or signals. There are special seconds-long sleeps put around the code so that he can ctrl-C at the proper moment xDDD. He boasts about no needing to write proper try-excepts "until sth blows up", but ofc nothing will blow up if one ignores all errors with `continue`s and without any error/warning logging. And, on top of that, some quiz questions or answers are unclear, including one that tries to make you choose whether `String.split`or `split` is a proper Python function to split a string, while neither exist at this point of strictness (because it is `str.split`).

April 03, 2023

great way to introduce general sql skills while focusing on text search and exploitation

February 07, 2023

The last two modules, the curse turn in the wrong direction, i decline the curse.

January 06, 2023

Assignments were frustrating due to a lack of explanation and clear instructions.

December 09, 2022

awesome!

October 07, 2022

The final assignment of this course was much difficult to understand as it was not mentioned in good way. And bacause of that it takes a time to complete the course. But Overall course and lectures, videos, and other assignments were interesting and very much self descriptive.

May 13, 2022

Could use some more examples on WHY we need JSON in a SQL setup

February 15, 2022

This module is really non sence. And it is a wasting of time. We are learning PostgreSQL and like idiot, we are facing python code to solve the assignement as we were in a python course. If you just start the PostgreSQL course, please do not waste your time with this module, you will spend time and money for nothing.

JSON and Natural Language Processing in PostgreSQL

What You'll Learn