Get a Customized Plan

The Fairway Technologies Blog

Querying JSON Data in Postgres

In JSON, data, PostgreSQL No Comments

In a recent project, my team had a chance to use the JSON features built into PostgreSQL. We discovered that these features are powerful and easy to use, so I wanted to share our experience here. Consider this tool for any project that is using a relational database and needs to work with complex, structured data.

The project's goal was to build an application for writing and reviewing test questions. These questions would appear on tests such as common-core in schools. The application needed to support a variety of question types—including multiple choice and questions where students must drag-and-drop options into the correct order. The structure would be unique for each type of question.

To keep the data representation simple and consistent across client, server, and database, we decided to represent the data in JSON format everywhere. This would avoid the need to translate data as it was moved. Here is a high-level overview of the architecture:

Screen Shot 2019-04-11 at 12.14.45 PM

Figure 1. High-level architecture

We selected PostgreSQL as the database to house this data since it was open source (a requirement from the client that had contracted this work), had native support for JSON data, was a good match for other application data needs, and because the team had previous PostgreSQL experience.

Creating a JSON column

The question data was stored in a JSON column in PostgreSQL. Here is how we created this table:

    CREATE TABLE question (
        id              serial   NOT NULL PRIMARY KEY,
        question_json   json     NOT NULL
    );

 

Querying a JSON column

To retrieve a question’s data, we would query the table and return the value from the JSON column:

SELECT question_json
FROM question
WHERE id = 1234;

The returned data was suitable for passing directly to the web app.

  

Inserting into a JSON column

The JSON string sent from the web app could be inserted directly into the JSON column without any changes (the value has been split across multiple lines here for easier viewing):

INSERT INTO question (question_json)
VALUES
(
    '{ '
    ' "type":    "multiple_choice", '
    ' "prompt":  "Who is the protagonist?", '
    ' "options": [{"The bear", "false"}, '
    '             {"The fox", "false"}, '
    '             {"The turtle", "true"}] '
    '}'
);

Querying JSON data

Initially, the project had no requirements to support searching or reporting against the question data. The developers and QA team, however, needed to find items meeting certain criteria from time to time (e.g. find all multiple-choice questions). This was easily done using the built-in JSON querying features.

SELECT id
FROM question
WHERE question_json -> 'type' = 'multiple_choice';

The project, eventually, did need to support fast searching and reporting against the question data. To avoid putting load on the PostgreSQL database that was servicing live reads and writes, we added a data warehouse where we extracted the contents of the question JSON and loaded it into tables and columns designed to support fast lookups.

Going further

The JSON support provided by PostgreSQL goes further than the examples given here. If you’re interested in learning about more advanced features, I recommend you take a look at the PostgreSQL tutorial: http://www.postgresqltutorial.com/postgresql-json/ . There you can learn about how aggregate functions work with JSON, casting JSON data, and other useful features.

New Call-to-action
New Call-to-action