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