Enable Dark Mode!
an-overview-of-json-in-postgresql.jpg
By: Rosmy John

An Overview of JSON in PostgreSQL

Technical

PostgreSQL introduced the JSON datatype in its 9.2 release to cater to the growing user demand for JSON support. This was a departure from PostgreSQL's usual cautious development pace. Under the hood, the JSON datatype is essentially text, but it comes with a validation check to ensure the input adheres to JSON format standards, similar to how XML is handled.
Both the JSON and JSONB data types accept almost the same input values. However, the key difference lies in efficiency. JSON stores an exact copy of the input text, which requires parsing with every execution of processing functions. In contrast, JSONB uses a binary format, making it slightly slower to input due to conversion overhead but significantly faster to process since it doesn't require reparsing. JSONB also supports indexing, which can be a significant advantage in certain scenarios.
Recognizing the need for advanced JSON processing and searching capabilities in PostgreSQL, the community developed JSONB as a binary version of the JSON datatype. JSONB includes a full set of operators and functions for working with JSON data, addressing incompatibilities with the text-based JSON datatype. The 'b' in JSONB stands for "better" due to its improved performance and functionality.
There exist notable disparities between the traditional text-based JSON datatype and its more recent counterpart, JSONB, where the 'b' has been construed as "better." Here's a breakdown of these distinctions:

JSON Datatype:

Storage: JSON, a text datatype, retains the data exactly as transmitted to PostgreSQL. This includes preserving whitespace, indentation, and even multiple keys within the JSON content. Essentially, no processing occurs beyond the basic validation of the JSON format.

JSONB Datatype:

Storage: JSONB, on the other hand, employs a sophisticated binary storage format. It preprocesses JSON data into an internal structure representing a single value per key. Unlike JSON, JSONB is not affected by extraneous whitespace or indentation in the JSON content.
Processing: 
JSONB offers comprehensive processing capabilities, including indexing and searching functionalities. This makes it significantly more efficient in terms of query execution.
In summary, while JSON maintains the original presentation of data and performs minimal processing, JSONB is a superior choice due to its binary storage format, which enhances processing speed, enables indexing, and disregards extraneous formatting details.

Functions and Operations

OperatorRight operand typeDescription
->intGet JSON array element (indexed from zero, negative integers count from the end)
->textGet JSON object field by key
->>intGet JSON array element as text
->>textGet JSON object field as text
#>text[]Get JSON object at the specified path
#>>text[]Get JSON object at the specified path as Text

JSONB Functions:

Within JSONB, a range of built-in methods are available for various operations. Let's explore them individually:

JSONB Each Function:

The JSONB Each function is a powerful tool for handling JSONB data. It takes JSONB data and transforms it into key-value pairs. Here's an example query using the jsonb_each method, where we provide input values. The highest-level JSON data is effectively expanded into a collection of key-value pairs in the result. In this case, we obtain two key-value pairs as demonstrated below:

SELECT jsonb_each('{"name": "Allure", "sold": "true"}'::jsonb );
jsonb_each
------------
(name, "Allure")
(sold, "true")

In this output, you can see that the JSONB Each function has efficiently converted the JSONB data into key-value pairs, making it more accessible and manageable.

JSONB Object Keys Function:

Let's now explore the JSONB Object Keys Function, known as jsonb_object_keys. This function plays a crucial role in parsing JSONB data by identifying and extracting the keys within it. In the following SELECT query example, we'll use the jsonb_object_keys method with some provided values. Its primary function is to return only the keys found at the highest level of the JSONB document. Here's the output:

SELECT jsonb_object_keys('{"name": "kidza", "sold": "true"}'::jsonb );
jsonb_object_keys
------------------
name
sold

As illustrated above, the jsonb_object_keys function adeptly identifies and returns the keys at the top level of the JSONB data, making it valuable for working with JSONB structures.

JSONB Extract Path Function:

Now, let's delve into the functionality of the JSONB Extract Path function, known as jsonb_extract_path. This function is employed to specify a path within JSONB data and retrieve the corresponding value. Below is a sample query that utilizes jsonb_extract_path, with 'brand' specified as the path. The output, as shown, highlights that 'Gucci' is the returned value associated with the 'name' path:

SELECT jsonb_extract_path('{"name": "Gucci", "sold": true}'::jsonb, 'name');

In this example, jsonb_extract_path efficiently extracts the value 'Gucci' from the JSONB data at the specified 'name' path, demonstrating its utility in navigating and retrieving specific data within JSONB structures.

How to extract JSONB type value in Odoo

JSONB, short for JSON Binary, stands as a data type within PostgreSQL, offering a means to store and interrogate JSON (JavaScript Object Notation) data in a binary format. This format grants advantages such as efficient storage and indexing features tailored for JSON documents. Let's explore several prevalent scenarios where JSONB finds application within PostgreSQL:

1. Schemaless Data Storage: JSONB serves as an ideal choice for the storage of data without rigid schemas. It accommodates structured, semi-structured, or unstructured data, making it particularly suitable for datasets with varying attributes or those that evolve over time.

2. Document Storage: Complex documents, such as user profiles, blog posts, or product descriptions, can be efficiently stored and retrieved using JSONB. This prevents the need to distribute document attributes across multiple tables, simplifying the retrieval and modification of entire documents in a single query.

3. Flexible Schema Evolution: JSONB allows for modifications to the data structure without necessitating alterations to the table schema. New fields can be introduced, existing ones can be modified or removed, all without the need for schema migrations. This flexibility is valuable for evolving data models or integrating with APIs that may introduce new fields over time.

4. Storing Configuration Settings: JSONB proves highly suited for storing configuration settings or preferences that can vary among users or across environments. Configuration data can be represented as a JSON object containing key-value pairs, and specific settings can be easily accessed and updated using PostgreSQL's JSON operators and functions.

5. Logging and Audit Trails: JSONB can be employed to store log entries or audit trails, capturing structured information about events or actions. Each log entry can be represented as a JSON object, enabling flexible and efficient querying based on specific attributes or criteria.

6. NoSQL-Like Querying: PostgreSQL equips JSONB with an extensive set of operators and functions for querying. This allows for filtering, searching, and aggregation operations directly on JSON data, resembling the querying capabilities of NoSQL databases.

7. Denormalized Data Storage: In some cases, denormalizing data by consolidating related attributes within a JSONB column can enhance performance. Instead of performing multiple joins to retrieve related data, fetching the entire denormalized JSONB document reduces query complexity and improves performance.

It's important to consider the trade-offs when opting for JSONB. For highly structured data with stringent schema requirements and frequent updates, a traditional relational schema may be more suitable. However, when dealing with dynamic data attributes, intricate structures, or evolving schemas, JSONB stands as a powerful tool within the PostgreSQL ecosystem.

To read more about PostgreSQL queries, refer to our blog An Introduction to PostgreSQL Queries


If you need any assistance in odoo, we are online, please chat with us.



0
Comments



Leave a comment

 


whatsapp
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

London

Cybrosys Limited
Alpha House,
100 Borough High Street, London,
SE1 1LB, United Kingdom

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message