Querying JSON data using Postgres

Introduction to NoSQL

Jake Roach

Data Engineer

Querying JSON data with Postgres

Two tables, one containing a single column of type JSON, and the other with the result of the query extract information from the parent_meta column.

-> operator

Takes a key, returns field as JSON

->> operator

Takes a key, returns field as text

SELECT
    parent_meta -> 'guardian' AS guardian
    parent_meta ->> 'status' AS status
FROM student;

Querying nested JSON objects

Two tables, one containing a single column of type JSON, and the other with the result of the query extract information from the parent_meta column.

To query nested JSON objects:

Use -> and ->> in tandem
First, return nested object
Then, extract the field

SELECT
    parent_meta -> 'jobs' ->> 'P1' AS jobs_P1,
    parent_meta -> 'jobs' ->> 'P2' AS jobs_P2
FROM student;

Querying JSON arrays

Two tables, one containing a single column of type JSON, and the other with the result of the query extract information from the parent_meta column.

Accessing JSON array elements:

Pass an INT to ->, returns field as JSON
Pass an INT to ->>, returns field as text

SELECT
    parent_meta -> 'educations' ->> 0
    parent_meta -> 'educations' ->> 1
FROM student;

Finding the type of data store in JSON objects

Two tables, one containing a single column of type JSON, and the other with the type of the JSON object output in a single column.

json_typeof function

Returns the type of the outermost object
Use with ->
Useful for debugging, building queries
Typically not used with the ->> operator

SELECT
    json_typeof(parent_meta -> 'jobs')
FROM students;

Review

SELECT
    -- Top-level fields
    <column-name> -> '<field-name>' AS <alias>,
    <column-name> ->> '<field-name>' AS <alias>,

    -- Nested fields
    <column-name> -> '<parent-field-name>' ->> '<nested-field-name>' AS <alias>,

    -- Arrays
    <column-name> -> '<parent-field-name>' -> 0 AS <alias>,
    <column-name> -> '<parent-field-name>' ->> 1 AS <alias>,

    -- Type of
    json_typeof(<column-name> -> <field-name>) AS <alias>
FROM <table-name>;

Let's practice!

Introduction to NoSQL