Storing JSON data in Postgres

Introduction to NoSQL

Jake Roach

Data Engineer

INSERT INTO and COPY JSON records to Postgres

INSERT INTO students (school, age, address, parent_meta) VALUES (
    'GP',
    18,
    'U',
    '{\"guardian\": \"mother\", ...  \"P2\": \"at_home\"}}'
);

Populate a table with contents of a file using COPY ... FROM

COPY students FROM 'students.csv' DELIMITER ',' CSV, HEADER;

Turning tabular data into JSON format

A table containing tabular data, and a table showing the first table in JSON format after using the row_to_json function.

row_to_json function

Converts a row to JSON
Use with the row() function, and pass column names

SELECT
    row_to_json(row(
        school,
        age,
        address
    ))
FROM students;

Extracting keys from JSON

Two tables; one with a single column of type Postgres JSON, and another containing the output of the json_object_keys function.

json_object_keys function

Extracts keys from a column of type JSON

SELECT
    json_object_keys(parent_meta)
FROM students;

Pair with DISTINCT to find all unique keys

SELECT
    DISTINCT json_object_keys(parent_meta)
FROM students;

Review

SELECT
    row_to_json(row(
        <column-1>,
        <column-2>,
        ...
    ))
FROM <table-name>;

SELECT
    DISTINCT json_object_keys(parent_meta)
FROM <table-name>;

Let's practice!

Introduction to NoSQL