Introduction
In this lab, you will explore PostgreSQL's advanced data types, focusing on JSON/JSONB, arrays, and UUIDs. You will learn how to store, query, and manipulate data within these types.
The lab begins by demonstrating how to store and query JSON and JSONB data, including creating a table with a JSONB column, inserting JSON data, and using operators like -> and ->> to extract specific values. You will then proceed to learn about array columns and UUIDs.
Store and Query JSON and JSONB Data
In this step, you will learn how to store and query JSON and JSONB data in PostgreSQL. PostgreSQL provides two data types for storing JSON data: JSON and JSONB. The JSON data type stores an exact copy of the JSON input text, while the JSONB data type stores JSON data in a decomposed binary format. JSONB is generally preferred because it offers better performance for querying and indexing.
Let's start by opening the PostgreSQL shell. First, connect to the labex database:
sudo -u postgres psql -d labex
You should see the PostgreSQL prompt:
labex=#
Now, let's create a table to store JSONB data:
CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);
This SQL command creates a table named products. The table has two columns: id (an auto-incrementing integer primary key) and data (a JSONB column to store JSON data).
You should see output similar to this:
CREATE TABLE
Now, let's insert some data into the products table:
INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');
These commands insert two rows into the products table. Each row contains a JSON object with information about a product.
You should see output similar to this for each insert:
INSERT 0 1
To query the JSON data, you can use the -> and ->> operators. The -> operator returns a JSON object, while the ->> operator returns a JSON value as text.
For example, to retrieve the name of the first product, you can use the following query:
SELECT data ->> 'name' FROM products WHERE id = 1;
This command selects the value associated with the key name from the data column of the products table, where the id is 1. The ->> operator ensures that the result is returned as text.
You should see output similar to this:
?column?
----------
Laptop
(1 row)
You can also query nested JSON objects. For example, to retrieve the first feature of the first product, you can use the following query:
SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;
This command first selects the features array from the data column, and then selects the element at index 0 from the array. The ->> operator ensures that the result is returned as text.
You should see output similar to this:
?column?
----------
16GB RAM
(1 row)
You can also use the @> operator to check if a JSON object contains a specific key-value pair. For example, to find all products with a price of 75, you can use the following query:
SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';
This command selects the name of all rows from the products table where the data column contains a JSON object with a price key and a value of 75.
You should see output similar to this:
?column?
----------
Keyboard
(1 row)
Great! We have successfully created our first table with JSONB data and learned how to query it. We'll continue working with this table in the next step to add more advanced features.
Add Array Columns to Existing Table
In this step, you will learn how to add and manipulate array columns in PostgreSQL. We'll extend our existing products table by adding an array column to store tags. Array columns allow you to store multiple values of the same data type in a single column, which is useful for storing lists of items such as tags, categories, or features.
Since we're already connected to the database and have our products table from the previous step, let's add array columns to our existing table:
ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];
These commands add two new columns to our existing products table: name (a string) and tags (an array of strings). The TEXT[] data type specifies that the tags column is an array of text values.
You should see output similar to this for each alter command:
ALTER TABLE
Now, let's update our existing data and insert new data with the new columns:
UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;
These commands update our existing products with name and tags information extracted from the JSONB data and new array values.
You should see output similar to this for each update:
UPDATE 1
To query the array data, you can use array indexing. Array indices in PostgreSQL start at 1.
For example, to retrieve the first tag of the first product, you can use the following query:
SELECT tags[1] FROM products WHERE id = 1;
This command selects the element at index 1 from the tags array of the products table, where the id is 1.
You should see output similar to this:
tags
-----------
electronics
(1 row)
You can also use the UNNEST function to expand an array into a set of rows.
For example, to retrieve all tags from all products, you can use the following query:
SELECT name, UNNEST(tags) AS tag FROM products;
This command selects the name and each individual tag from the tags array, creating a new row for each tag.
You should see output similar to this:
name | tag
---------+-------------
Laptop | electronics
Laptop | computers
Laptop | portable
Keyboard| electronics
Keyboard| accessories
Keyboard| input
(6 rows)
You can use the @> operator to check if an array contains a specific value.
For example, to find all products with the tag 'electronics', you can use the following query:
SELECT name FROM products WHERE tags @> ARRAY['electronics'];
This command selects the name of all rows from the products table where the tags array contains the value 'electronics'.
You should see output similar to this:
name
----------
Laptop
Keyboard
(2 rows)
You can also use the && operator to check if two arrays have any elements in common.
For example, to find all products that share any tags with the first product, you can use the following query:
SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;
This command selects the name of all rows from the products table (aliased as p2) that have at least one tag in common with the tags of the first product (aliased as p1), excluding the first product itself.
You should see output similar to this:
name
----------
Keyboard
(1 row)
Perfect! We have successfully added array columns to our existing table and learned how to work with arrays. Our products table now contains both JSONB and array data types, making it ready for the next step.
Add UUID Column and Learn UUID Generation
In this step, you will learn how to generate and use UUIDs (Universally Unique Identifiers) in PostgreSQL. UUIDs are 128-bit numbers that are designed to be unique across space and time. They are often used as unique identifiers in database tables to avoid conflicts when merging data from different sources.
Since we're continuing with our existing session and table, we'll add a UUID column to our current products table to demonstrate UUID functionality.
First, let's enable the UUID extension that provides UUID generation functions:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
This command creates the uuid-ossp extension if it doesn't already exist.
You should see output similar to this:
CREATE EXTENSION
Now, let's add a UUID column to our existing products table:
ALTER TABLE products ADD COLUMN uuid_id UUID;
This command adds a new UUID column to our existing products table.
You should see output similar to this:
ALTER TABLE
Now we can update our existing rows with UUID values using the uuid_generate_v4() function:
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;
These commands update our existing products with unique UUID values. The uuid_generate_v4() function generates a new UUID for each row.
You should see output similar to this for each update:
UPDATE 1
To query the data using UUIDs, you can use the UUID values in your WHERE clauses. Since UUIDs are randomly generated, let's first view our current data:
SELECT id, name, uuid_id FROM products;
This command selects all columns from the products table to see the generated UUIDs alongside our other data.
You should see output similar to this (your UUID will be different):
id
------------------------------------
a1b2c3d4-e5f6-7890-1234-567890abcdef
(1 row)
Now, use that UUID in the following command, replacing <YOUR_UUID_HERE> with the actual UUID you retrieved:
SELECT name FROM products WHERE id = '<YOUR_UUID_HERE>';
This command selects the name from the products table where the id matches the specified UUID.
You should see output similar to this (depending on which UUID you picked):
name
----------
Laptop
(1 row)
Finally, let's clean up the table and extension we created:
DROP TABLE products;
DROP EXTENSION "uuid-ossp";
You should see output similar to this for each drop command:
DROP TABLE
DROP EXTENSION
Extract Data from Advanced Types
In this final step, you will practice extracting data from all the advanced data types we've added to our products table throughout this lab. Our table now contains JSONB, array, and UUID columns, providing a comprehensive example of PostgreSQL's advanced data types.
Since we're continuing with our existing session and table, we can immediately start working with the data we've been building up through the previous steps.
First, let's add some additional JSONB data to make our extraction examples more comprehensive:
UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;
These commands update our existing JSONB data with more detailed information, including nested objects and additional fields.
You should see output similar to this for each update:
UPDATE 1
Now let's practice extracting data from all our advanced data types. To extract data from the JSONB data column, you can use the -> and ->> operators. For example, to extract the brand from our updated data:
SELECT data ->> 'brand' FROM products WHERE id = 1;
This command retrieves the value associated with the key brand from the data column.
You should see output similar to this:
?column?
----------
Dell
(1 row)
To extract nested data from the JSONB column, you can chain the -> and ->> operators. For example, to extract the RAM specification:
SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;
This command retrieves the value associated with the key ram within the specs object.
You should see output similar to this:
?column?
----------
16GB
(1 row)
To extract data from the tags (array) column, you can use array indexing, as learned in Step 2. For example, to extract the first tag of the first product:
SELECT tags[1] FROM products WHERE id = 1;
This command retrieves the element at index 1 from the tags array of the products table where the id is 1.
You should see output similar to this:
tags
-----------
electronics
(1 row)
Now let's create a comprehensive query that extracts data from all our advanced data types - JSONB, arrays, and UUIDs:
SELECT
id,
name,
data ->> 'brand' AS brand,
data -> 'specs' ->> 'ram' AS ram,
tags[1] AS first_tag,
uuid_id
FROM products;
This command retrieves data from all the advanced data types we've worked with: the integer id, the name, the brand from the JSONB data column, nested ram specification, the first element of the tags array, and the uuid_id.
You should see output similar to this:
name | ?column? | tags
----------+------------+-----------
Laptop | Dell | electronics
Keyboard | Logitech | electronics
(2 rows)
Excellent! You have successfully worked with all three advanced PostgreSQL data types in a single table. This comprehensive example demonstrates how JSONB, arrays, and UUIDs can be used together to create flexible and powerful database schemas.
When you're done with all the steps, you can exit the PostgreSQL shell by typing:
\q
You can also choose to keep the table for further experimentation with PostgreSQL's advanced data types.
Summary
In this lab, you progressively built and worked with a comprehensive products table that demonstrates PostgreSQL's advanced data types in action. You started by creating a table with JSONB columns and learned how to store and query JSON data using operators like -> and ->>.
You then extended the table by adding array columns, learning how to store multiple values in a single column and query them using array indexing and functions like UNNEST. Next, you added UUID functionality by enabling the uuid-ossp extension and adding a UUID column to generate unique identifiers.
Finally, you practiced comprehensive data extraction techniques that combined all three advanced data types - JSONB, arrays, and UUIDs - in sophisticated queries. This step-by-step approach demonstrated how these data types can work together in real-world database schemas, providing flexibility and powerful querying capabilities for modern applications.
