[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'; ```