Detailed explanation of the use example of json type field in Postgresql database

The most important documentary data type in PostgreSQL isJSONWith MongoDBBSONIn comparison, PostgreSQL may be more powerful because it is compatible with the original relational paradigm, bringing more feasibility and convenience to database storage and maintenance.

1. Json Overview

JSON stands for JavaScript Object Notation. JSON is an open standard format consisting of key-value pairs. JSON is mainly used to transfer data between servers and web applications. Create a new table as follows:

2. Use Json type fields in Postgresql database

2.1. Create table definition field information

CREATE TABLE "test" (  
    "id" int8 ,
    "info" json NOT NULL
);

2.2. Add

INSERT INTO "test"("id", "info") VALUES (1, '{"showcolor":"#C1E498"}');
INSERT INTO "test"("id", "info") VALUES (2, '{"showcolor":"#C1E497"}');
INSERT INTO "test"("id", "info") VALUES (3, '{"showcolor":"#C1E496"}');

2.3. Query key values

2.3.1. Query key

SELECT info -> 'showcolor' AS color FROM test;

2.3.2. Query value

SELECT info ->> 'showcolor' AS color FROM test;

2.3.3. Where query conditions use json key value as condition

SELECT
   info ->> 'showcolor' AS color
FROM
   test
WHERE
   info ->> 'showcolor' = '#C1E496'

This is the article about the use of json type fields in Postgresql database. For more related content on Postgresql json type fields, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!