[TOC] ## JSON 類型 | 名字 | 描述 | | --- | --- | | json | JSON 按文本保存輸入數據，保留原始數據中的空格 | | jsonb |按照二進制保存輸入數據，他會刪除沒必要的空格以及重復的鍵 | 原生支持的類型 ``` 對象 數組 字符串 數字 true false null ``` ### 創建sql ``` CREATE TABLE login_logs ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, detail JSON NOT NULL ); ``` ### 插入數據 ``` INSERT INTO login_logs (detail) VALUES ('{ "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}}'), ('{ "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}}'), ('{ "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}}'); ``` ### 查詢json結果 ``` > SELECT * FROM login_logs; id | detail ----+--------------------------------------------------------------------- 1 | { "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}} 2 | { "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}} 3 | { "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}} (3 rows) ``` 查詢分別使用兩個運算符 -> 和 ->> 以 JSON 的形式獲取所有登陸用戶的名稱 ``` SELECT detail -> 'name' AS name_json, detail ->> 'name' AS name_text FROM login_logs; ``` 返回 ``` name_json | name_text -----------+----------- "Tom" | Tom "Tim" | Tim "Jim" | Jim (3 rows) ``` ### where 條件查詢 ``` SELECT detail ->> 'name' AS name, detail -> 'address' ->> 'country' AS country FROM login_logs WHERE detail -> 'address' ->> 'country' = 'US'; ```