# JSON and JSONB Fields in PostgreSQL
JSON (JavaScript Object Notation) and JSONB (Binary JSON) are data types in PostgreSQL that allow storing and manipulating JSON data. JSON is a lightweight data interchange format, while JSONB is a binary representation of JSON data, providing additional functionality and performance optimizations. Let's explore JSON and JSONB in PostgreSQL and go through some basic CRUD (Create, Read, Update, Delete) operations.
### 1. JSON Data Type:
The JSON data type stores JSON-formatted data as text. It validates the JSON syntax but does not enforce any constraints on the structure or keys within the JSON document.
Example: Creating a table with a JSON column.
```sql
CREATE TABLE users (
id serial PRIMARY KEY,
data json
);
```
CRUD Operations with JSON:
- Create: Inserting a JSON value into a column.
```sql
INSERT INTO users (data)
VALUES ('{"name": "John", "age": 30}');
```
- Read: Retrieving JSON values from a column.
```sql
SELECT data->>'name' AS name, data->>'age' AS age
FROM users;
```
- Filter by on JSON value.
```sql
SELECT *
FROM users
WHERE data->>'age' >= '30';
```
- Update: Modifying JSON values within a column.
```sql
UPDATE users
SET data = jsonb_set(data, '{age}', '"31"')
WHERE id = 1;
```
- Delete: Removing a JSON value from a column.
```sql
UPDATE users
SET data = data - 'age'
WHERE id = 1;
```
### 2. JSONB Data Type:
The JSONB data type stores JSON data in a binary format, allowing for efficient storage and indexing. It supports all JSON functionality and provides additional capabilities, such as indexing, faster searching, and query optimization.
Example: Creating a table with a JSONB column.
```sql
CREATE TABLE products (
id serial PRIMARY KEY,
data jsonb
);
```
CRUD Operations with JSONB:
- Create: Inserting a JSONB value into a column.
```sql
INSERT INTO products (data)
VALUES ('{"name": "Product 1", "price": 10.99}');
```
- Read: Retrieving JSONB values from a column.
```sql
SELECT data->>'name' AS name, data->>'price' AS price
FROM products;
```
- Filter by on JSONB value.
```sql
SELECT *
FROM users
WHERE data->>'age' >= '30';
```
- Update: Modifying JSONB values within a column.
```sql
UPDATE products
SET data = jsonb_set(data, '{price}', '"9.99"')
WHERE id = 1;
```
- Delete: Removing a JSONB value from a column.
```sql
UPDATE products
SET data = data - 'price'
WHERE id = 1;
```
### Difference between -> and ->> operators
In PostgreSQL, the `->` and `->>` operators are used to extract values from JSON or JSONB data. However, they differ in their output and usage:
1. `->` Operator:
The `->` operator is used to extract a JSON object or element from a JSON or JSONB column. It returns the value as JSON data type.
Example:
```sql
SELECT data->'name' AS name
FROM users;
```
This query retrieves the value associated with the key "name" from the JSON/JSONB column `data`.
2. `->>` Operator:
The `->>` operator is used to extract a JSON object or element from a JSON or JSONB column and returns the value as text.
Example:
```sql
SELECT data->>'name' AS name
FROM users;
```
This query retrieves the value associated with the key "name" from the JSON/JSONB column `data` and returns it as text.
The main difference between `->` and `->>` is the data type of the returned value. The `->` operator returns the value as JSON, allowing for further manipulation or extraction of nested elements using additional JSON operators. On the other hand, the `->>` operator returns the value as text, suitable for direct display or comparison purposes.
It's important to note that both `->` and `->>` can be used with JSON and JSONB columns interchangeably since they have the same behavior for JSON and JSONB data types.
In summary, the `->` operator returns the value as JSON, while the `->>` operator returns the value as text when extracting elements from JSON or JSONB columns in PostgreSQL.
### JSON vs JSONB
| Aspect | JSON | JSONB |
|-------------------|--------------------------------------------------------------|-------------------------------------------------------------------|
| Storage Format | Plain text | Binary format |
| Query Performance | Parsing on-the-fly, can impact performance | Faster searching, indexing, and querying |
| Storage Size | Larger due to plain text storage | Smaller due to binary storage |
| Data Modification | Full value replacement for updates | Supports in-place updates for efficient modifications |
| Indexing | No direct indexing support | Supports indexing for efficient querying and searching |
| Use Case | Simple JSON structures, minimal querying/indexing needs | Efficient querying, indexing, and manipulation of complex JSON data |
### Conclusion:
JSON and JSONB data types in PostgreSQL provide flexible storage and manipulation of JSON-formatted data. JSON is stored as plain text, while JSONB is a binary representation offering enhanced performance and indexing capabilities. With these data types, you can perform basic CRUD operations to create, read, update, and delete JSON or JSONB values within your tables.
Google Drive
Gist
Clipboard
Sign in via Facebook
Sign in via X(Twitter)
Sign in via GitHub
Sign in via Dropbox
Sign in with Wallet
