![]() ![]() In this article, I would like to address the key differences and use cases. However, I have seen many people inadvertently featuring Postgres 11 as “The New NoSQL” or that they don’t need any NoSQL database as they are already using Postgres. In my personal opinion, it will help developers to be more familiar with all the advantages of storing data as JSON instead of plain old tables. Postgres has been my favorite RDBMS for years, and I’m thrilled with the increasing popularity of its JSONB support. The user can get data from the JSONB column using different operators like “ ->” and “ This guide has explained the JSONB data type in PostgreSQL and different examples to use it.The reality is that databases are converging, and in the last few years, it is becoming even more difficult to point what are the best scenarios for each datastore without a deep understanding of how things work under the hood. A JSONB data type column can store a list of data in a single field like the example from this guide displays the name and hobbies in the same column. JSONB data type is used to create columns in the PostgreSQL table like other types such as integer, character, etc. That’s all about the JSONB data type in the PostgreSQL table. The above query displays that both employees have the hobby “ Movies” in their data list: Use the following query to get the list of employees who has the hobby of watching “ Movies”: SELECT emp_id, data FROM emp The above query only displays the data of the employee whose hobby list contains “ Gaming” data: Use the “ operator to get data from the employee table with reference to a particular hobby: SELECT emp_id, data FROM emp The above code displays the list of all the hobbies with reference to the emp_id column as each hobby belongs to a particular employee:Įxample 5: Use Operator With WHERE Clause Use the following query to get the hobbies but with their representation according to the emp_id column: SELECT emp_id, jsonb_array_elements_text(data->'hobbies') Running the above query displays the list of all the hobbies in a separate cell: Use the following code to get the list of all the hobbies stored in the table: SELECT jsonb_array_elements_text(data->'hobbies') The above code will display only hobbies from the data column of the employee “ Marc”: Use the following command to get the specific key’s data from the selected column: SELECT data->'hobbies' FROM emp Use the following query to get the list of hobbies from the data column: SELECT data->'hobbies' FROM emp Įxecuting the above code has displayed the list of hobbies for both employees:Įxample 3: How to Fetch Specific Data From a JSONB Column Running the above code only displays the names of the employees from the JSONB column: Use the following command containing the -> operator to get the name from the data column and display it on the screen: SELECT data->'name' FROM emp The " ->" operator in Postgres retrieves the value of a specific key/index from a JSONB object or array stored in a JSONB field. Running the above code displays the data stored in the emp table containing the JSONB datatype:Įxample 2: How to Fetch the Specific Key of a JSONB Object ![]() ![]() Type the following query to get all the data stored in the emp table: SELECT * FROM emp The above code inserts two rows containing the information regarding employees’ ids, names, and hobbies: Once the table is created successfully, the user needs to insert values in it using the following query: INSERT INTO emp VALUES The data field has the datatype of the jsonb so it can contain multiple values or a list in the field: The emp_id column has the int data type and it has the constraint of not having a NULL value at any time. The above code creates a PostgreSQL table called “ emp” and it contains two columns that are emp_id and data. Use the following query to create a table that uses JSONB data type for one of its columns: CREATE TABLE emp ( It also enhances/improves performance significantly as it simplifies schema designs and provides efficiency by creating indexes on JSONB columns.Įxample 1: How to Create a Table With JSONB Data Type JSONB in PostgreSQL is a data type that can be used to store data in decomposed binary format. It was becoming difficult for PostgreSQL to process and analyze the huge amount of textual form of data so they opted for the binary form of JSON. This guide will explain JSONB data types in the PostgreSQL database. JSON text is human-readable and PostgreSQL supports the JSON data types while creating tables or updating columns in the PostgreSQL table. It is used when the data is sent from the server to a particular web page and the user can extract data from the text using JSON syntax. JSON or JavaScript Object Notation is a lightweight format for storing and transporting data in the form of text. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |