PostgreSQL – JSON Data Type
The json data type is used to store JSON (JavaScript Object Notation) data. JSON is a widely used data interchange format that represents structured data in a human-readable text format. PostgreSQL introduced native support for the json data type to store and manipulate JSON documents directly within the database. Here are some key points about the json data type:
- Storing JSON Data: The
jsondata type allows you to store JSON-formatted data in columns. JSON data can represent objects, arrays, strings, numbers, booleans, and null values. - Querying and Manipulating JSON Data: PostgreSQL provides various functions and operators to query, manipulate, and extract data from JSON values stored in
jsoncolumns. - JSON Syntax: JSON values are represented in the database as valid JSON syntax, with curly braces for objects
{}and square brackets for arrays[]. - JSONB Data Type: PostgreSQL also offers the
jsonbdata type, which is similar tojsonbut provides binary storage and indexing for faster querying of JSON data.jsonbsupports all JSON values and functions, while providing improved performance for certain operations.
Here’s an example of using the json data type:
CREATE TABLE product_catalog(product_id serial PRIMARY KEY,product_data json );
INSERT INTO product_catalog (product_data)VALUES('{"name": "Widget A","price": 19.99,"in_stock": true}');
SELECT product_data->>'name' AS name, product_data->>'price' AS price FROM product_catalog WHERE product_data->>'in_stock' = 'true';In this example, a product_data column of type json is used to store product information in JSON format. The JSON data type is useful when you need to store flexible and complex data structures directly within your PostgreSQL database.