Postgres 字段 Json 类型查询

Postgres 支持两种 json 数据类型：json 和 jsonb

json 是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等
jsonb 是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同，使用时不用再次解析
效率的差别：json类型存储快，使用慢 ；jsonb类型存储稍慢，使用较快

info:
{
	"name":"名字",
	"age":"年龄"
}

id: ["123","456"]

一、连接配置

&stringtype=unspecified 用于把 JSON 类型当 String 类型存储

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://127.0.0.1:5432/demo?useUnicode=true&characterEncoding=UTF-8&stringtype=unspecified
    username: postgres
    password: postgres

二、查询语句

查询 name = bai 的这条记录

select * from user where info ->> 'name' = 'bai';

查询数组中第二个元素

select info ->> 1 from user;

查询 age > 16 的记录，并且只显示 name

select info -> 'name' from user where (info ->> 'age')::int4 > 16;

查询 info 这条记录里的顶层 json 中有没有 age = 12 的 key-value 对，有返回 t , 否则返回 f

select info from user where info @> '{"age":12}'::jsonb;

查询 id = 123 这条记录是否存在 json 数组中，返回数量

select count(1) from user, json_array_elements_text(id::json) ids where ids = '123';

查询数组的长度

select jsonb_array_length(info::jsonb) from user;

查询所有的 name

-- "张三"
select json_array_elements(info::json) -> 'name' as name from user;

-- 张三
select json_array_elements(info::json) ->> 'name' as name from user;

-- "张三"
select json_array_elements(info::json) #> '{name}' as name from user;

select distinct names from (select json_array_elements(info::json) #> '{name}' as names from user) u;

三、操作符

操作符	右操作数类型	描述	示例	结果
->	int	获取 JSON 数组元素（索引从0开始）	select ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json -> 2	{“c”:“baz”}
->	text	text 通过键获取值	select ‘{“a”: {“b”:“foo”}}’::json -> ‘a’	{“b”:“foo”}
->>	int	获取 JSON 数组元素为 text	select ‘[1,2,3]’::json ->> 2	3
->>	text	通过键获取值为 text	select ‘{“a”:1,“b”:2}’::json ->> ‘b’	2
#>	text[]	在指定的路径获取 JSON 对象	select ‘{“a”: {“b”:{“c”: “foo”}}}’::json #> ‘{a,b}’	{“c”: “foo”}
#>>	text[]	在指定的路径获取 JSON 对象为 text	select ‘{“a”:[1,2,3],“b”:[4,5,6]}’::json #>> ‘{a,2}’	3
||	jsonb	jsonb 连接两个 JSON 对象，组成一个新的 JSON 对象	select ‘[“a”, “b”]’::jsonb || ‘[“c”, “d”]’::jsonb	[“a”,“b”,“c”,“d”]