最近工作需要使用 sql 语句进行 Postgresql json 类型字段的查询，本文特此记录下一些常用的函数。
Postgresql json 类型简介 postgresql 支持两种 json 数据类型：json 和 jsonb，而两者唯一的区别在于效率，json 是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而 jsonb 是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json 类型存储快，使用慢，jsonb 类型存储稍慢，使用较快。
json 类型操作符 我们先介绍 json 和 jsonb 的一些常用通用操作符：
操作符 右操作数类型 描述 示例 结果 -> int 获取 JSON 数组元素（索引从 0 开始） select ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json->2; {“c”:“baz”} -> 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 jsonb 独有的操作符 操作符 右操作数类型 描述 示例 结果 @> jsonb 左侧 json 最上层的值是否包含右边 json 对象 select ‘{“a”:{“b”:2}}’::jsonb @> ‘{“b”:2}’::jsonb;
select ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb; f
t <@ jsonb 左侧 json 对象是否包含于右侧 json 最上层的值内 select ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb; t ? text text 是否作为左侧 Json 对象最上层的键 select ‘{“a”:1, “b”:2}’::jsonb ? ‘b’; t ?| text[] text[]中的任一元素是否作为左侧 Json 对象最上层的键 select ‘{“a”:1, “b”:2, “c”:3}’::jsonb ?| array[‘b’, ‘c’]; t ?& text[] text[]中的所有元素是否作为左侧 Json 对象最上层的键 select ‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’]; t || jsonb 连接两个 json 对象，组成一个新的 json 对象 select ‘[“a”, “b”]’::jsonb || ‘[“c”, “d”]’::jsonb; [“a”, “b”, “c”, “d”] - text 删除左侧 json 对象中键为 text 的键值对 select ‘{“a”: “b”}’::jsonb - ‘a’; {} - integer 删除数组指定索引处的元素，如果索引值为负数，则从右边计算索引值。如果最上层容器内不是数组，则抛出错误。 select ‘[“a”, “b”]’::jsonb - 1; [“a”] 创建 json 类型 那我们该如何创建 json 类型呢，下面介绍一些常见的函数
...