JSON data type in PostgreSQL: How to store JSON data

Introduction

The integration of JSON as a first-class data type in PostgreSQL provides a powerful tool for developers, enabling them to store structured and semi-structured data seamlessly within a relational database system.

Understanding JSON Data Types in PostgreSQL

PostgreSQL offers two JSON-related data types: json and jsonb. The json data type stores JSON input as plain text, without processing it, while jsonb stores JSON data in a decomposed binary format which is slower to insert but faster to query due to its indexing capabilities.

CREATE TABLE example (
    id serial PRIMARY KEY,
    data json
);

CREATE TABLE example_b (
    id serial PRIMARY KEY,
    data jsonb
);

Storing JSON Data

To store JSON data, you can insert it just as you would any other scalar value:

INSERT INTO example (data) VALUES ('{"name": "John", "age": 30}');

For jsonb, you can use the same syntax:

INSERT INTO example_b (data) VALUES ('{"name": "John", "age": 30}');

Querying JSON Data

PostgreSQL provides operators to extract and manipulate JSON data:

SELECT data->'name' AS name FROM example WHERE id = 1;

To query nested JSON data:

SELECT data->'address'->>'street' AS street FROM example WHERE id = 1;

With jsonb, you can utilize GIN indexes to rapidly query data:

CREATE INDEX idx_gin_data ON example_b USING gin (data);

Advanced JSON Functions

PostgreSQL offers many functions for more sophisticated operations on JSON data. For instance, you can use json_agg to aggregate multiple rows into a JSON array:

SELECT json_agg(data) FROM example;

Updating a JSON value in a jsonb column:

UPDATE example_b SET data = jsonb_set(data, '{name}', '"Jane"') WHERE id = 1;

Transforming and Combining Data

PostgreSQL allows you efficiently transform JSON data and combine it with traditional relational data. This is done using functions like json_to_record to convert JSON objects into rows:

SELECT * FROM json_to_record(data) AS x(name text, age int) WHERE id = 1;

Performance Considerations

It is important to consider the performance trade-offs between json and jsonb. Use jsonb for datasets that require indexing and fast lookups, and json for full-fidelity storage where manipulation is not needed.

Key-Value Operations

JSONB supports key-value operations naturally. You can topple the value of a key quickly, without affecting other parts of the JSON document:

UPDATE example_b SET data = data || '{"city":"New York"}' WHERE id = 1;

Combining JSON and Relational Data

PostgreSQL’s JSON capabilities allow you to quantity both JSON/JSONB data and traditional relational columns in the same query, offering immense flexibility:

SELECT name, data->>'city' as city FROM example JOIN example_b ON example.id = example_b.id WHERE data->>'name' = 'John';

Conclusion

PostgreSQL’s support for JSON data types affords tremendous possibilities for application developers. By using json for unstructured data and jsonb for performance-critical applications, one can harness the best of both the relational and NoSQL worlds in tandem.