笔记——PostgreSQL操作JSON数据

前言

---

PostgreSQL提供了两种存储JSON数据的类型：json和jsonb；

• jsonb是json的二进制形式。
• json格式写入快，但读取慢；
• jsonb格式写入慢，但读取快；

常用语法

---

 // -> 返回json
 select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 // 输出 {"c":"baz"}
 select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'a' // 输出 {"b":"foo"}
 
 // ->> 返回文本
 select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->>2 // 输出 {"c":"baz"}
 select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->>'a' // 输出 {"b":"foo"}

 // #> 获取json子对象
 select '{"a": {"b":{"c": "foo"}}}'::json#> '{a,b}' // 输出 {"c": "foo"}
 select '{"a": {"b":{"c": "foo"}}}'::json#>> '{a,b}' // 输出 {"c": "foo"}

 // @> ———— 判断第一个json是否包含第二个
 select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb   //输出t
 // <@ ———— 判断第一个json是否在第一个中
 select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb   //输出t

常用操作符

---

json&jsonb	右操作数据类型	备注
->	int	获取json数组的元素
->	text	通过key值获取json对象字段
->>	int	获取json数组元素为字符串
->>	text	获取json对象字段为字符串
#>	text[ ]	在指定路径下获取json对象
#>>	text[ ]	在指定路径下获得json对象为字符串

jsonb	右操作数据类型	备注
@>	jsonb	在顶层，左边的json值包含右边的json值
<@	jsonb	在顶层，右边的json值包含左边的json值
ll	jsonb	将两个jsonb值连接成一个新jsonb值

实例操作

---

数据库表(test_pgsql)：

第一条data为json类型的数组：

[
	{
	    "username": "张三",
	    "age": "18",
	    "sex": "男"
	  },
	{
	    "username": "张三",
	    "age": "25",
	    "sex": "男"
	  },
	{
	    "username": "马冬梅",
	    "age": "20",
	    "sex": "女"
	  }
]

第二条data内容为json类型的对象：

{
    "course":[
        {
            "id":"1",
            "course":"语文",
            "score":"75"
        },
        {
            "id":"2",
            "course":"数学",
            "score":"100"
        }
    ],
    "student":{
        "name":"小王",
        "age":"22"
    }
}

1、查询数组的长度：

// 结果为4
select jsonb_array_length(t."data"::jsonb) from test_pgsql t WHERE t.id = 1;

2、查询数组中第二个元素

//数组索引从0开始，结果为{"age": "25", "sex": "男", "username": "张三"}
select t."data"::jsonb->>1 from test_pgsql t WHERE t.id = 1;

3、查询data中所有的username

select json_array_elements(t."data"::json) #> '{username}' as guid from test_pgsql t where t."id" = 1;

//username去重
select distinct guid|| '' from (select json_array_elements(t."data"::json) #> '{username}' as guid from test_pgsql t where t."id" = 1) tmp;

结果：

4、json_object_keys 用法

• 说明：json_object_keys 不能用于纯数组

//获取json中的键
select json_object_keys(t."data"::json) from test_pgsql t where id = 2;

结果：

5、json_array_elements 用法

//json_array_elements用于提取转换纯数组元素，将数组拆分为单独记录
select json_array_elements(t."data"::json) from test_pgsql t WHERE t.id = 1;

结果：

6、json_extract_path 用法

• 说明：json_extract_path不能直接操作纯数组

//查询json中指定键(student)的值，结果为 {"name": "小王", "age": "22"}
select json_extract_path(t."data"::json, 'student') from test_pgsql t where id = 2;
//结果为 "小王"
select json_extract_path(t."data"::json, 'student','name') from test_pgsql t where id = 2;

//和 #>操作符 是一样的
select t."data"::json #>'{student}' from test_pgsql t where id = 2;
select t."data"::json #>'{student,name}' from test_pgsql t where id = 2;