Search⌘ K
Home
Courses
The Art of PostgreSQL

Solution Review: JSON Data Type

Explore how to work with the JSONB data type in PostgreSQL by creating a cars table, inserting JSON data, querying based on JSON content, and updating nested JSON fields. Understand key SQL commands and operators to manipulate denormalized JSON data for flexible database solutions.

We'll cover the following...

In the challenge, you were asked to perform three tasks. Here, we’re going to discuss the solution to these three tasks.

Task 1

You were asked to create a cars table and insert data into it.

Solution

The DDL for creating the table and inserting records in the table is given below. You can check the data using the SELECT query given in the following playground. Click the “Run” button in the following widget to see the output of the code.

PostgreSQL
/********************************************************/
/*****    Write the queries for creating a table    *****/
/*****    and inserting the data into the table     *****/
/********************************************************/
CREATE TABLE cars (  
  car_id serial unique,
  car_info jsonb
);
INSERT INTO cars 
     VALUES (1, '{ "brand": "ferrari", "model": "testarosa" }'),
            (2, '{ "brand": "aston martin", "model": "db2" }'),
            (3, '{ "brand": "bentley", "model": "mulsanne" }'),
            (4, '{ "brand": "ford", "model": "T" }');
            
SELECT * FROM cars;
/********************************************************/

Explanation

The explanation for task one of the challenge is given below: ...