Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

PostgreSQL: JSON Data Type

PostgreSQL supports JSON type (Java Script Object Notation). JSON is an open standard format which contains key-value pairs and it is human-readable text.

PostgreSQL supports JSON data type since the 9.2 version. It also provides many functions and operators for processing JSON data.

The following table includes the JSON type column.

Example: A Table with JSON Type
CREATE TABLE Person_json (
	id SERIAL NOT NULL PRIMARY KEY,
	info JSON NOT NULL
);

The above Person_json table has two columns, the id column is a primary key column that identifies the person. The info column has information about a person in the format of JSON. Note that we defined the info column as NOT NULL, so it must be populated with a valid JSON value.

INSERT JSON data

Now to insert data to the Person_Json table, the data of the info column must be in valid JSON format.

Example: Insert JSON Data
INSERT INTO person_json(info)
VALUES
	('{"name":"Annie Smith","details":{"gender": "F","age": 23}}'),
	('{"name":"May Kaasman","details":{"gender": "M","age": 17}}'),
	('{"name":"Charlton Duran","details": {"gender": "M","age": 37}}'),
	('{"name":"Susan Klassen","details": {"age": 45}}');

The above shows, it inserted 4 rows to the Person_json table. You can see we inserted nested Json objects to table column.

A point to note here is that the fourth row JSON is ('{"name": "Susan Klassen", "details": {"age": 45}}') where details JSON does not have a gender column defined. which is there for the other three rows. The INSERT statement does not throw any error. The INSERT statement only validates data to be in JSON format.

As PostgreSQL validates data in JSON format for JSON-defined columns during insertion, If you try to insert some text value that is in non-JSON format, you will get an error like below.

Example: Invalid JSON Data
INSERT INTO Person_json(info)
VALUES
	('Non-Json text value');

Querying JSON data

Tfollowing select query gets the data from the Person_json table.

PostgreSQL provides two native operators -> and ->> to help you query JSON data.

The operator -> returns the JSON object field by key.

Example: JSON Data
SELECT info -> 'name' AS name FROM person_json;

The operator ->> returns the JSON object field by text.

Example: JSON Data
SELECT info ->> 'name' AS name FROM person_json;

As the -> operator returns JSON objects, you can further chain it with the ->> operator to retrieve specific detail of the nested JSON object. Here we are querying gender and age information from nested JSON objects as below

Example: JSON Data
SELECT info -> 'name' AS name, 
	info -> 'details' ->> 'gender' AS gender,
	info -> 'details' ->> 'age' AS age
FROM person_json;

Use JSON Operators in WHERE clause

The JSON operators -> and ->> can be used in the WHERE clause to filter out data. For example, let's find out all Female persons from table Person_Json table.

Example: JSON Operators in WHERE Clause
SELECT info -> 'name' as NAME
FROM person_json
WHERE info -> 'details' ->> 'gender' = 'F';

The following query finds out all persons above 25 years of age.

Example: JSON Operators in WHERE Clause
SELECT info -> 'name' as NAME
FROM person_json
WHERE CAST(info -> 'details' ->> 'age' AS NUMERIC) > 25;

PostgreSQL JSON Processing Functions

PostgreSQL supports some in-built functions for processing JSON column data.

Json_each()

The json_each() function allows to extract outermost JSON object as a set of key-value pairs.

Example: json_each()
SELECT json_each (info)
FROM person_json;

Use the json_each_text() function to return same result as text.

Json_type_of()

The json_type_of() function returns the data type of the outermost JSON value as a string. The values can be string, number, Boolean, null, array, or object.

Example: json_type_of()
SELECT json_typeof (info -> 'details' -> 'age')
FROM person_json;

Json_object_keys()

Use the json_object_keys function to get a set of keys in the outermost JSON object. The following query returns all keys of nested details object from the info JSON column.

Example: json_object_keys()
SELECT json_object_keys (info -> 'details')
FROM person_json;
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.