Understanding JSON data in Postgres

Introduction to NoSQL

Jake Roach

Data Engineer

JSON and JSONB in Postgres

{
    'guardian': 'mother',
    'status': 'A',
    'educations': [4, 4],
    'jobs': {
        'P1': 'teacher',
        'P2': 'at_home'
    }
}

CREATE TABLE students
    <column-name> JSON,
    <column-bame> JSONB
;

JSON

  • Store data in JSON format
  • Key-values pairs, arrays, nested objects

$$

JSONB

  • Data is stored in binary format
  • Similar to BSON in MongoDB
  • More efficient storage and retrieval
  • Allows for additional indexing
Introduction to NoSQL

Why semi-structured data in Postgres?

A Postgres table containing a column of type JSON.

Introduction to NoSQL

Querying JSON data with Postgres

SELECT
    address,
    famsize,
    ...
FROM students
[WHERE | GROUP BY | ORDER BY];

$$

  • row_to_json, json_to_record
  • ->, ->>, #>, #>> operators
  • json_extract_path, json_extract_path_text

We'll also be able:

  • Insert JSON-formatted records to a Postgres table
  • Tabular to JSON, JSON to tabular
  • Extract individual records from JSON objects
Introduction to NoSQL

Executing queries with sqlalchemy and pandas

import sqlalchemy
import pandas as pd

# Create a connection
db_engine = sqlalchemy.create_engine(
    "postgresql+psycopg2://<user>:<password>@<host>:5432/<database>"
)

# Write a query
query = "SELECT * FROM table_name;"

# Execute the query, show results
results = pd.read_sql(query, db_engine)
Introduction to NoSQL

Let's practice!

Introduction to NoSQL

Preparing Video For Download...