How to define and query json columns in PostgreSQL?

The ability to define JSON columns in PostgreSQL makes it very powerful and helps PostgreSQL users experience the best of both worlds: SQL and NoSQL.

Creating JSON columns is quite straightforward. You just have to create/ define it like any other column and use the data type as JSON.

Let us create a new table in PostgreSQL, called json_test −

CREATE TABLE json_test(
   serial_no SERIAL PRIMARY KEY,
   name VARCHAR,
   metadata JSON
);

Now, let us populate it with some data −

INSERT INTO json_test(name, metadata)
VALUES ('Yash','{"marks_scored":{"science":50,"maths":65}}'),
('Isha', '{"marks_scored":{"science":70,"maths":45}}');

As you can see, the JSON values are added within single quotes, just like we add VARCHAR/TEXT values.

Now, if you query the table (SELECT * from json_test), you will see the following output −

However, we can do better than this. Suppose I want to know the marks scored in science by both Yash and Isha. All I need to do is use the -> operator. See the example below −

SELECT name, metadata->'marks_scored'->'science' as science_marks
from json_test

The output will be

Please note that over here, the output column science_marks is of type JSON and not INTEGER. This is because → operator always returns a json. Apart from the → operator, the->> operator is also commonly used. The difference between the two is that while → returns a json, ->> returns a text.

Thus,

• metadata→'marks_scored'→'science' will return a JSON, even though we have integers for science_marks

• metadata→'marks_scored'->>' science' will return text

• metadata->>'marks_scored'→'science' will give an error. Because the 'marks_scored' output is no longer JSON, and thus, the → operator doesn’t work on it.

If you explicitly want the science_marks in integer format, you first get the result in text format, and then cast it to an integer, as shown below −

SELECT name, CAST(metadata->'marks_scored'->>'science' as integer)
as science_marks from json_test

Note that you cannot cast JSON to an integer. You need to use the ->> operator at the last step to get text output, and only then can you cast the text to an integer.

Just like you can use the JSON columns in the select part of the query, you can also use them in the WHERE part of the query. If we wish to find out the students who have scored > 60 marks in Science, your query would look like

SELECT name from json_test
WHERE CAST(metadata->'marks_scored'->>'science' as integer) > 60

And the output would be