JSON and Natural Language Processing in PostgreSQL

JSON and Natural Language Processing in PostgreSQL

This course is part of PostgreSQL for Everybody Specialization

Instructor: Charles Russell Severance

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.

Skills you'll gain

Details to know

Shareable certificate

Add to your LinkedIn profile

Assessments

2 assignments

Taught in English

See how employees at top companies are mastering in-demand skills

Learn more about Coursera for Business

Build your subject-matter expertise

This course is part of the PostgreSQL for Everybody Specialization

When you enroll in this course, you'll also be enrolled in this Specialization.

Learn new concepts from industry experts
Gain a foundational understanding of a subject or tool
Develop job-relevant skills with hands-on projects
Earn a shareable career certificate

Earn a career certificate

Add this credential to your LinkedIn profile, resume, or CV

Share it on social media and in your performance review

There are 4 modules in this course

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.

In this first week, we introduce you to the course and its main focus: text and JSON.

What's included

9 videos5 readings4 app items

9 videosTotal 84 minutes

Welcome to the Course!2 minutesPreview module
Allocating Rows to Blocks in PostgreSQL9 minutes
Index Implementation Details15 minutes
Building an Inverted Index with SQL6 minutes
Demonstration: SQL Inverse Index10 minutes
Building a Natural Language Index with SQL6 minutes
Demonstration: SQL Natural Language Index17 minutes
Bonus: Office Hours - Seoul, South Korea9 minutes
Bonus: Office Hours - Portland, Oregon, USA5 minutes

5 readingsTotal 45 minutes

Help Us Learn More About You5 minutes
Syllabus10 minutes
Each Course Uses a Different Database10 minutes
How Assignments Work10 minutes
Connecting to Your Database Server10 minutes

4 app itemsTotal 240 minutes

Making our First Tables60 minutes
Building an Inverted Index using SQL60 minutes
Building an Inverted Index with stop words using SQL60 minutes
Initial Database Setup60 minutes

In this week, we'll look at GIN-based inverted indexes and ts_vector() and ts_query() functions.

What's included

7 videos1 reading1 assignment2 app items

7 videosTotal 45 minutes

A GIN-based Inverted Index with PostgreSQL10 minutesPreview module
Demonstration: GIN-based inverted index5 minutes
Building a Natural Language Index in PostgreSQL9 minutes
Demonstration: Fulltext tsquery and tsvector Functions6 minutes
Demonstration: Building a GIN / tsvector Index5 minutes
Office Hours - Washington, DC, USA3 minutes
Office Hours, Amsterdam, Netherlands3 minutes

1 readingTotal 5 minutes

Assessment Philosophy5 minutes

1 assignmentTotal 15 minutes

Text In Databases15 minutes

2 app itemsTotal 120 minutes

Building a string array-based GIN index60 minutes
Building a tsvector-based full text GIN index60 minutes

This week is all about connecting and comparing Python and PostgreSQL.

What's included

10 videos2 readings2 app items

10 videosTotal 81 minutes

Lecture: PostgreSQL and Python5 minutesPreview module
Demonstration Python and PostgreSQL simple.py16 minutes
Demonstration loadbook.py12 minutes
Lecture: Mail Archive4 minutes
Demonstration: Mail Archive 1/316 minutes
Demonstration: Mail Archive 2/310 minutes
Lecture: Ranking Search Results with PostgreSQL4 minutes
Demonstration: Mail Archive 3/37 minutes
Office Hours: Zagreb, Croatia3 minutes
Office Hours, Orlando, Florida, USA1 minute

2 readingsTotal 20 minutes

How Python Assignments Work10 minutes
Python and PostgreSQL on PythonAnywhere10 minutes

2 app itemsTotal 120 minutes

Running simple.py60 minutes
A Sequence of Numbers60 minutes

In this final week, we'll focus primarily on JSON and its functions.

What's included

11 videos2 readings1 assignment1 app item

11 videosTotal 100 minutes

Lecture: JavaScript Object Notation10 minutesPreview module
Interview: Douglas Crockford - JSON7 minutes
Lecture: Python and JSON6 minutes
Lecture: PostgreSQL and JSON11 minutes
Demonstration: Music Tracks and JSON26 minutes
Lecture: Using a JSON API2 minutes
Demonstration: Star Wars API 1/216 minutes
Demonstration: Star Wars API 2/215 minutes
Course Wrap Up0 minutes
Office Hours, London, England1 minute
Office Hours - Montreal, Quebec, Canada (at PyCon)0 minutes

2 readingsTotal 15 minutes

Douglas Crockford and JSON10 minutes
Post-Course Survey5 minutes

1 assignmentTotal 30 minutes

JSON and PostgreSQL30 minutes

1 app itemTotal 60 minutes

Interacting with the PokéAPI60 minutes

Instructor

Instructor ratings

4.1 (20 ratings)

Charles Russell Severance

University of Michigan

60 Courses4,421,702 learners

Offered by

University of Michigan

Why people choose Coursera for their career

Felipe M.

Learner since 2018

"To be able to take courses at my own pace and rhythm has been an amazing experience. I can learn whenever it fits my schedule and mood."

Jennifer J.

Learner since 2020

"I directly applied the concepts and skills I learned from my courses to an exciting new project at work."

Larry W.

Learner since 2021

"When I need courses on topics that my university doesn't offer, Coursera is one of the best places to go."

Chaitanya A.

"Learning isn't just about being better at your job: it's so much more than that. Coursera allows me to learn without limits."

Learner reviews

4.6

130 reviews

5 stars
76.92%
4 stars
13.07%
3 stars
6.15%
2 stars
0.76%
1 star
3.07%

Showing 3 of 130

Reviewed on Jan 9, 2022

Week 4 was a titbit challenging, but I figure it out. On to the next one. Lol

Reviewed on Dec 30, 2024

Excellent course and instructor - I would highly recommend to anyone interested in exploring the 'new-ish' json/jsonb/inverted index support in PostgreSQL.

Reviewed on May 21, 2021

This course teaches some niche applications. Not sure if i'll ever have to use them, but good to know what to look for in case it ever comes up at work :)

View more reviews