How to Query a JSON Column in PostgreSQL

As a software engineer, it is common to store structured data in a relational database such as PostgreSQL, or unstructured data in a non-relational database such as Mongo DB.

Luckily, PostgreSQL allows defining column types as json and jsonb, which are two JSON (JavaScript Object Notation) data types that allow storing unstructured data in a relational database. This can bring some different notations in the way you fetch data from a JSON column.

In this article, I’ll explain the different ways you can query data from a JSON column in PostgreSQL. For the sake of visibility, below is the query of the table schema I will use to show examples of how to query data from a JSON column.

CREATE TABLE test (data json);

INSERT INTO test (data) VALUES ('{ "user": { "first_name": "John", "last_name": "Doe", "age": 4 } }');
INSERT INTO test (data) VALUES ('{ "user": { "first_name": "Super", "last_name": "Mario", "age": 20 } }');

Based on the previous query, I defined the column data as json for this tutorial.

Querying a JSON column

To query a JSON column without specifying a possible key-value pair from a record, use the traditional SELECT column_name FROM table_name notation.

SELECT data FROM test;

This will return data with the type of json like you see in the example below.

Querying a specific key-value pair from a JSON Column

Using the traditional SELECT column_name FROM table_name notation works, but sometimes you just want to fetch a specific key-value pair from a record stored in a JSON column.

Also, depending on the kind of record stored in the database, you could end up with JSON arrays and JSON object data. I’ll explain how to fetch specific key-value pair data from.

Querying data from a JSON object to return json or jsonb data type

To fetch a specific key-value pair from a JSON object and expect a json or jsonb returned data type from a JSON column use the -> operator followed by the name of the key.

SELECT data -> 'user' AS user FROM test;

Below is the result of the previous SQL query.

To verify the data returned is indeed json or jsonb, use the pg_typeof function.

SELECT pg_typeof(data -> 'user') AS user_type FROM test;

Querying data from a JSON object to return text data type

To fetch a specific key-value pair from a JSON object and expect a text returned data type from a JSON column use the ->> operator followed by the name of the key.

SELECT data ->> 'user' AS user FROM test;

Below is the result of the previous SQL query.

The previous result looks similar to the result when using the -> operator. However, the data type returned is different. To verify the data returned is text, use the pg_typeof function.

SELECT pg_typeof(data ->> 'user') AS user_type FROM test;

Querying data from a JSON array to return json or jsonb data type

To fetch a specific key-value pair from a JSON array and expect a json or jsonb returned data type from a JSON column, use the -> operator followed by the index element of an array. Then, use again the -> operator followed by the name of the key.

For the purposes of this example, I’m going to insert a new record that represents a JSON array.

INSERT INTO test (data) VALUES ('[{ "user": { "first_name": "John", "last_name": "Doe", "age": 4 } }, { "user": { "first_name": "Super", "last_name": "Mario", "age": 20 } }]');

Now, I’ll fetch the first element of the array by the ‘user’ key.

SELECT data -> 0 -> 'user' AS user FROM test;

Below is the result of the previous SQL query.

Below is another example in case I’d want to fetch instead the ‘user’ key of the second element of the array.

SELECT data -> 1 -> 'user' AS user FROM test;

Below is the result of the previous SQL query.

Once again, to verify the data returned is indeed json or jsonb, use the pg_typeof function.

SELECT pg_typeof(data -> 1 -> 'user') AS user FROM test;

Querying data from a JSON array to return text data type

To fetch a specific key-value pair from a JSON array and expect a text returned data type from a JSON column, use the -> operator followed by the index element of an array. Then, use again the ->> operator followed by the name of the key.

Similar to the previous section, for the purposes of this example, I’m going to insert a new record that represents a JSON array.

INSERT INTO test (data) VALUES ('[{ "user": { "first_name": "John", "last_name": "Doe", "age": 4 } }, { "user": { "first_name": "Super", "last_name": "Mario", "age": 20 } }]');

Now, I’ll fetch the second element of the array by the ‘user’ key.

SELECT data -> 1 ->> 'user' AS user FROM test;

Below is the result of the previous SQL query.

Finally, let’s confirm the data returned is text type using our good function pg_typeof function.

SELECT pg_typeof(data -> 1 ->> 'user') AS user FROM test;