Postgres type of JSON/JSONB

First, Introduction

JSON is a full copy of the input, and then parse it, so it preserves the input spaces, repetition keys, and order. The JSONB is a binary that is saved after parsing the input, and it removes unnecessary spaces and duplicate keys when parsing, and the order and input may not be the same. Make time without parsing again.

Summarize:

.	JSON	jsonb
Save	Fast	Slow
Take	Slow	Fast

Ii. Practice 1, definition

Here we have defined 姓名 and 毕业院校 two fields

CREATE TABLE "Students"(   name VARCHAR(255),   edu_experience JSON)

2. Inserting (1) Postgres SQL

INSERT INTO "Students"VALUES(    ‘colin‘,    ‘{"name":"清华大学","year":"2000","remark":"难忘的经历"}‘)

(2) Sequelize

{"Name": "Tsinghua University", "Year": "The Year", "remark": "Unforgettable Experience"}

3, take the first kind: take all

"edu_experience": {    "name": "清华大学",    "year": "2000",    "remark": "难忘的经历"}

Second type: In-depth fetch (1) Postgres SQL

A.return "Tsinghua University" (with double quotes)

SELECT"edu_experience"->‘name‘FROM "Students"  WHERE id = 152

B.return Tsinghua University (without double quotes)

SELECT"edu_experience"->>‘name‘FROM"Students" WHEREid = 152

Note: -> Returns a JSON object that ->> returns text , if in a nested object, such as edu_experience the value of the field is an object:

{    "info": {        "items" : {            "product" : "xxx"        }    } }

Take out the wording of XXX:

(2) Sequelize

await models.Student.findOne({     attributes: [[models.sequelize.json("edu_experience.name"), "edu_exp_name"]]})

Return

{    "edu_exp_name": "清华大学"}

Note: attributes: [models.sequelize.json("edu_experience.name")] this is not a correct notation, you must give the value given as a rename

4. Enquiry

Basic query It's too lazy to say

Expand

CAST

For example: To figure out who purchased two products at a time?

SELECT info ->> ‘customer‘ AS customer, info -> ‘items‘ ->> ‘product‘ AS productFROM ordersWHERE CAST ( info -> ‘items‘ ->> ‘qty‘ AS INTEGER ) = 2

Note: We use type conversion to CAST convert the Qty field to an INTEGER type and compare it to 2

Resources:

http://www.postgresqltutorial.com/postgresql-json/

Postgres type of JSON/JSONB