Database Management · Advanced level

Advanced level · Module 5: NoSQL and Choosing a Data Store · Lesson 2 of 4

Document Data and JSON in PostgreSQL

Store and query flexible JSON documents, both in a document database and in PostgreSQL's JSONB columns.

In this lesson you will

  • Query JSON documents in a document database
  • Store and query JSONB in PostgreSQL with indexes

Key concepts

JSON documentsJSONB-> and ->> operatorsGIN index

The lesson

Survey forms change over time and different surveys ask different questions, so their responses suit flexible documents. A document database stores each response as a document and queries fields inside it.

PostgreSQL's JSONB type stores JSON efficiently in a normal table, so flexible data can sit next to strict relational data. answers->>'water_source' extracts a field as text, and a GIN index speeds up searches inside documents.

Keep identifiers and data you report on often in normal columns with constraints, and use JSONB for the variable remainder.

Step by step

  1. Insert survey responses into a document database and query a field.
  2. Store the same responses in a PostgreSQL JSONB column.
  3. Query fields with ->> and add a GIN index.
  4. Compare the two approaches.

Practice activity

Complete the survey storage comparison from the practical activity.

Expected result: Both implementations answer the same questions, with a written comparison of querying and integrity.

Check your understanding

  1. What does the ->> operator return?

    Show answer

    A JSON field's value as text.

  2. Which data should stay in normal columns rather than JSONB?

    Show answer

    Identifiers and data that are often filtered, joined or reported on.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.