对Postgresql中的json和array使⽤介绍
结合近期接触到的知识点，做了⼀个归纳。会持续更新
json
json的两种格式
总结：json输⼊快，处理慢。是精准拷贝，所以能准确存储遗留对象的原格式，如对象键顺序。jsonb输⼊慢，处理快。会被重新解析成json数据，不保存原对象的键顺序，并且去重相同的键值，以最后⼀个为准。通常，除⾮有特别特殊的需要（例如遗留的对象键顺序假设），⼤多数应⽤应该更愿意把 JSON 数据存储为jsonb
官⽹：
json 和 jsonb数据类型接受***⼏乎***完全相同的值集合作为输⼊。主要的实际区别之⼀是效率。json数据类型存储输⼊⽂本的精准拷贝，处理函数必须在每次执⾏时必须重新解析该数据。⽽jsonb数据被存储在⼀种分解好的⼆进制格式中，它在输⼊时要稍慢⼀些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也⽀持索引，这也是⼀个令⼈瞩⽬的优势。
由于json类型存储的是输⼊⽂本的准确拷贝，其中可能会保留在语法上不明显的、存在于记号之间的空格，还有 JSON 对象内部的键的顺序。还有，如果⼀个值中的 JSON 对象包含同⼀个键超过⼀次，所有的键/值对都会被保留（处理函数会把最后的值当作有效值）。相反，jsonb不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输⼊中指定了重复的键，只有最后⼀个值会被保留。
通常，除⾮有特别特殊的需要（例如遗留的对象键顺序假设），⼤多数应⽤应该更愿意把 JSON 数据存储为jsonb
由于json类型存储的是输⼊⽂本的准确拷贝，其中可能会保留在语法上不明显的、存在于记号之间的空格，还有 JSON 对象内部的键的顺序。还有，如果⼀个值中的 JSON 对象包含同⼀个键超过⼀次，所有的键/值对都会被保留（处理函数会把最后的值当作有效值）。相反，jsonb不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输⼊中指定了重复的键，只有最后⼀个值会被保留。
-----------1.键的顺序
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
json
-------------------------------------------------
{"bar": "baz", "balance": 7.77, "active":false}
(1 row)
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
(1 row)
---------2.去重
SELECT '{"bar": "baz", "balance": 7.77, "balance":false}'::jsonb; --去重
{"bar": "baz", "balance": false}
SELECT '{"bar": "baz", "balance": 7.77, "balance":false}'::json; --不去重
{"bar": "baz", "balance": 7.77, "balance":false}
select '[1, 2, 2]'::jsonb --数组不去重
[1, 2, 2]
将字符串转为json格式
sq-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;
-- 有零个或者更多元素的数组（元素不需要为同⼀类型）
SELECT '[1, 2, "foo", null]'::json;
-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
输出：
5
[1, 2, “foo”, null]
{“bar”: “baz”, “balance”: 7.77, “active”: false}
{“foo”: [true, “bar”], “tags”: {“a”: 1, “b”: null}}
判断是否包含/存在 @> 和 ?
-- 简单的标量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb; --得真t
-
- 右边的数字被包含在左边的数组中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb; --t
-- 数组元素的顺序没有意义，因此这个例⼦也返回真：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb; --t
-- 重复的数组元素也没有关系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb; --t
-- 右边具有⼀个单⼀键值对的对象被包含在左边的对象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb; --t
jsonb还有⼀个存在操作符，它是包含的⼀种变体：它测试⼀个字符串（以⼀个text值的形式给出）是否出现在jsonb值顶层的⼀个对象键或者数组元素中。
除⾮特别注解，下⾯这些例⼦返回真：
-- 字符串作为⼀个数组元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; --t
-- 字符串作为⼀个对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo'; --t
-- 不考虑对象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- 得到假
当涉及很多键或元素时，JSON 对象⽐数组更适合于做包含或存在测试，因为它们不像数组，进⾏搜索时会进⾏内部优化，并且不需要被线性搜索。
索引
-> 数组中是查第⼏项，json中是取某key的元素object
->> json中是取某key的元素text
#>  json中是通过路径取元素object
#>>  json中是通过路径取元素text
json:='[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json
--获取json数组中的某⼀项元素
--下标从0开始
select json->2 from test where name='jsonarray' --{"c": "baz"}
--获取json某⼀key的值为object
select (json->2)->'c' from test where name='jsonarray' --"baz"
--获取json某⼀key的值为text
select (json->2)->>'c' from test where name='jsonarray' --"baz"
--获取json某值通过路径 object
'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' --{"c": "foo"}
--获取json某值通过路径 text
'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' --3
补充：postgresql 数据库 jsonb/json中 array或int 类型进⾏的交集⽐较存储过程字符串、整数数组条件查询
⾸先要新增这两个存储过程
新增存储过程字符串数组条件查询
CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json) RETURNS text[] AS
$$
DECLARE
anyArray text[];
begin
SELECT ARRAY(SELECT json_array_elements_text(_js)) INTO anyArray;
RETURN anyArray;
end
$$
LANGUAGE plpgsql;
查询字符串数组中存在NP1的记录
select * from tb_template_area_safe WHERE json_arr2text_arr(area_functions) @> array['NP1'];
新增存储过程整数数组条件查询
CREATE OR REPLACE FUNCTION json_arr2int_arr(_js json) RETURNS int[] AS
$$
DECLARE
anyArray int[];
begin
存储过程 字符串转数组SELECT ARRAY(SELECT json_array_elements_text(_js)::int) INTO anyArray;
RETURN anyArray;
end;
$$
LANGUAGE plpgsql;
查询条件是左⾯的包含右⾯的
两者的交集 &&
SELECT tdnm.mid, tdnm.title, t, tdnm.ui_id, ate_time, tdnm.cancel_time,
tdnm.job_ids, arks, ssage_level_code, tdnm.channels FROM tb_data_notify_message
tdnm WHERE 1=1 and json_arr2text_arr(tdnm.job_ids)
&&array[['1','10']] ORDER BY create_time DESC
数组操作符:
Operator Description Example Result
=equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]t
<>not equal ARRAY[1,2,3] <> ARRAY[1,2,4]t
<less than ARRAY[1,2,3] < ARRAY[1,2,4]t
>greater than ARRAY[1,4,3] > ARRAY[1,2,4]t
<=less than or equal ARRAY[1,2,3] <= ARRAY[1,2,3]t
>=greater than or equal ARRAY[1,4,3] >= ARRAY[1,4,3]t
@>contains ARRAY[1,4,3] @> ARRAY[3,1]t
<@is contained by ARRAY[2,7] <@ ARRAY[1,7,4,2,6]t
&&overlap (have elements in common)ARRAY[1,4,3] && ARRAY[2,1]t
||array-to-array concatenation ARRAY[1,2,3] || ARRAY[4,5,6]{1,2,3,4,5,6}
||array-to-array concatenation ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]{{1,2,3},{4,5,6},{7,8,9}} ||element-to-array concatenation  3 || ARRAY[4,5,6]{3,4,5,6}
||array-to-element concatenation ARRAY[4,5,6] || 7{4,5,6,7}
以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。如有错误或未考虑完全的地⽅，望不吝赐教。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。