postgresql数据库json数据类型

简介

 JSON数据类型可以用来存储JSON（JavaScript Object Notation）数据，而JSON数据格
式是在RFC 4627中定义的。当然也可以使用text、varchar等类型来存储JSON数据，但使
用这些通用的字符串格式将无法自动检测字符串是否为合法的JSON数据。而且，JSON数
据类型还可以使用丰富的函数。

PostgreSQL 提供存储JSON数据的两种类型：json 和 jsonb。 为了实现这些数据类型高效的查询机制。json 和 jsonb数据类型接受几乎完全相同的值集合作为输入。 主要的实际区别之一是效率。

json数据类型:

存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。

jsonb数据类型：

被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势。

 

处理和构建JSON数据

创建测试数据和测试表

--创建测试表
CREATE TABLE text_json (
    id SERIAL PRIMARY KEY,
    json_col JSON,
    jsonb_col JSONB,
    json_array_col JSON[],
    jsonb_array_col JSONB[]
);  
--插入测试数据

INSERT INTO text_json (json_col, jsonb_col, json_array_col, jsonb_array_col)
VALUES
    ('{"name": "John", "age": 30}', '{"name": "John", "age": 30}', 
     ARRAY['{"name": "John", "age": 30}', '{"name": "Jane", "age": 25}']::json[], 
     ARRAY['{"name": "John", "age": 30}', '{"name": "Jane", "age": 25}']::jsonb[]),
    ('{"name": "Jane", "age": 25}', '{"name": "Jane", "age": 25}', 
     ARRAY['{"name": "Jane", "age": 25}', '{"name": "Mike", "age": 35}']::json[], 
     ARRAY['{"name": "Jane", "age": 25}', '{"name": "Mike", "age": 35}']::jsonb[]),
    ('{"name": "Mike", "age": 35}', '{"name": "Mike", "age": 35}', 
     ARRAY['{"name": "Mike", "age": 35}', '{"name": "Sarah", "age": 28}']::json[], 
     ARRAY['{"name": "Mike", "age": 35}', '{"name": "Sarah", "age": 28}']::jsonb[]),
    ('{"name": "Sarah", "age": 28}', '{"name": "Sarah", "age": 28}', 
     ARRAY['{"name": "Sarah", "age": 28}', '{"name": "David", "age": 40}']::json[], 
     ARRAY['{"name": "Sarah", "age": 28}', '{"name": "David", "age": 40}']::jsonb[]),
    ('{"name": "David", "age": 40}', '{"name": "David", "age": 40}', 
     ARRAY['{"name": "David", "age": 40}', '{"name": "Emily", "age": 22}']::json[], 
     ARRAY['{"name": "David", "age": 40}', '{"name": "Emily", "age": 22}']::jsonb[]),
    ('{"name": "Emily", "age": 22}', '{"name": "Emily", "age": 22}', 
     ARRAY['{"name": "Emily", "age": 22}', '{"name": "Daniel", "age": 33}']::json[], 
     ARRAY['{"name": "Emily", "age": 22}', '{"name": "Daniel", "age": 33}']::jsonb[]),
    ('{"name": "Daniel", "age": 33}', '{"name": "Daniel", "age": 33}', 
     ARRAY['{"name": "Daniel", "age": 33}', '{"name": "Olivia", "age": 27}']::json[], 
     ARRAY['{"name": "Daniel", "age": 33}', '{"name": "Olivia", "age": 27}']::jsonb[]),
    ('{"name": "Olivia", "age": 27}', '{"name": "Olivia", "age": 27}', 
     ARRAY['{"name": "Olivia", "age": 27}', '{"name": "James", "age": 31}']::json[], 
     ARRAY['{"name": "Olivia", "age": 27}', '{"name": "James", "age": 31}']::jsonb[]),
    ('{"name": "James", "age": 31}', '{"name": "James", "age": 31}', 
     ARRAY['{"name": "James", "age": 31}', '{"name": "Sophia", "age": 29}']::json[], 
     ARRAY['{"name": "James", "age": 31}', '{"name": "Sophia", "age": 29}']::jsonb[]),
    ('{"name": "Sophia", "age": 29}', '{"name": "Sophia", "age": 29}', 
     ARRAY['{"name": "Sophia", "age": 29}', '{"name": "Liam", "age": 32}']::json[], 
     ARRAY['{"name": "Sophia", "age": 29}', '{"name": "Liam", "age": 32}']::jsonb[]);

json操作符

json -> integer → json

jsonb -> integer → jsonb

提取JSON数组的第n个元素(数组元素从0开始索引，但负整数从末尾开始计数)。

json ->> integer → text

jsonb ->> integer → text

提取JSON数组的第n个元素，作为text。

select array_to_json(json_array_col) ->0
,array_to_json(jsonb_array_col) ->1,
array_to_json(json_array_col) ->>0
,array_to_json(jsonb_array_col) ->>1 from text_json;

 

json -> text → json

jsonb -> text → jsonb

用给定的键提取JSON对象字段

 

json ->> text → text

jsonb ->> text → text

用给定的键提取JSON对象字段，作为text。

select  json_col ->>'name',jsonb_col->>'name',json_col ->'name',jsonb_col->'name' from text_json;

 

相较于->操作符 只是返回类型不一致。

新增测试字段

--新增嵌套数据列
alter table text_json add column json_qt JSON;
alter table text_json add column jsonb_qt JSONB;
 
-- 插入嵌套json数据

update text_json set json_qt ='{
    "name": "John",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "New York",
        "state": "NY",
        "country": "USA",
        "contacts": [
            {
                "name": "Jane",
                "phone": "123-456-7890"
            },
            {
                "name": "Bob",
                "phone": "987-654-3210"
            }
        ]
    }
}';

update text_json set jsonb_qt='{
    "name": "John",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "New York",
        "state": "NY",
        "country": "USA",
        "contacts": [
            {
                "name": "Jane",
                "phone": "123-456-7890"
            },
            {
                "name": "Bob",
                "phone": "987-654-3210"
            }
        ]
    }
}';

json #> text[] → json

jsonb #> text[] → jsonb

提取指定路径下的JSON子对象，路径元素可以是字段键或数组索引。

 

json #>> text[] → text

jsonb #>> text[] → text

将指定路径上的JSON子对象提取为text。

select json_qt #>'{address,contacts,0}',jsonb_qt #>'{address,contacts,1}' 
,json_qt #>>'{address,contacts,0}',jsonb_qt #>>'{address,contacts,1}' 
from text_json limit 5;

相较于->操作符 只是返回类型不一致。

 

 jsonb相较于json数据类型有更多的操作符。 

select jsonb_col@>'{"age": 30}',--判断jsonb字段中是否含有{"age": 30}键值对，含有返回true 反之亦然。
'{"name": "John", "age": 30}' @>jsonb_col--判断jsonb字段中是否等于'{"name": "John", "age": 30}'完整的json值，含有返回true 反之亦然。
, jsonb_col ?'age' --判断jsonb字段中是否含有age键，含有返回true 反之亦然。
,jsonb_col ?| array['age','n'] --判断jsonb字段中是否含有array['age','是否']其中一个键,这里支持(或，与)的逻辑运算符(|,&),两个管道符||表示拼接 ，含有返回true 反之亦然。
,jsonb_col -'age'  --移除age键的键值对进行展示 
from text_json  ;

json函数 

函数名	描 述	示 例	结 果
to_json(anyelement)	把 任 意 类 型 的 对 象 转 换成JSON对象，数组被转换成JSON中的数组，复合类型被转换成JSON中的key/value对	to json(array[1,2,3]	[1,2,3]
		CREATE  TYPE  person  AS(    name  text,    age integer,    sex  boolean); select to_json('("osdba",40,true)::person);	to json {"name":"osdba","age":40,"sex":true} (1 row)
array_to_json(anyarray  [,pretty bool])	把数组转换成JSON对象	select array_to_json(array[1,2]);	array to json [1,2]
		select array_to_json(array[1,2],  true);	array to json [1,     + 2]
row_to_json(record [, pretty bool])	把 R O W 类 型 转 换 成 J S O N类 型	to json(ROW(1,2,3))	{"f1":1,"f2":2,"f3":3}
json_build_array (any,…)	构造JSON对象，其内容是由不同类型元素组成的数组	json build array(1,2,'3',true,null)	[1,2,"3", true,null]
json_build_object(any,.)	构造JSON对象，其内容是一个字典。字典的key/value对分别从函数参数中取得	json_build_object('a',1,'b',2,'c',array[3,4])	{"a":1,"b":2,"c":[3,4]}