postgresql 的json 和jsonb 的使用

1. json 和jsonb 区别

两者从用户操作的角度来说没有区别，区别主要是存储和读取的系统处理（预处理）和耗时方面有区别。

json写入快，读取慢，jsonb写入慢，读取快。

查询操作推荐使用jsonb，会让你的json查询效率有个很大的提升，这是血和泪的教训。

2. 常用的操作符

操作符：

-> // 右边传入整数（针对纯数组），获取数组的第n个元素，n从0开始算，返回值为json

json示例： select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 // 输出 {"c":"baz"}

-> // 右边传入键值（针对关联数组），获取数组的第n个元素，n从0开始算，返回值为json

json示例： select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'a' // 输出 {"b":"foo"}

->> // 右边传入整数（针对纯数组），获取数组的第n个元素，n从0开始算，返回值为文本
json示例： select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->>2 // 输出 {"c":"baz"}

->> // 右边传入键值（针对关联数组），获取数组的第n个元素，n从0开始算，返回值为文本

json示例： select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->>'a' // 输出 {"b":"foo"}

#> // 获取json子对象，传入数组，返回json

json示例： select '{"a": {"b":{"c": "foo"}}}'::json#> '{a,b}' // 输出 {"c": "foo"}

#>> // 获取json子对象并转换为文本，传入数组，返回文本

json示例： select '{"a": {"b":{"c": "foo"}}}'::json#>> '{a,b}' // 输出 {"c": "foo"}

2.1、 -> 和 ->> ：

-> 表示获取一个JSON数组元素，支持下标值(下标从0开始)、Key获取。->> 表示获取一个JSON对象字符串。

代码：

SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1;

结果：

{"b":2}

以上，::JSON 表示声明前面的字符串为一个JSON字符串对象，而且PostgreSQL中的JSON、JSONB对象 Key的声明必须是字符串 。同时，1表示获取JSON数组中下标值为1的JSON对象。

接下来，看下 ->> 的用法：

代码：

SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1;

结果：

{"b":2}

以上，->> 的查询结果和 -> 对比不太直观，我们可以进一步验证。

代码：

SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1 -> 'b';

结果：

2

以上，我们可以看到首先我们使用下标的方式，获取JSON数组中下标值为1的JSON对象 {"b":2}。然后，我们通过Key的方式来获取这个JSON对象的Value值，结果是 2。

接下来，我们测试下 ->> 的方式来获取：

代码：

SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';

报错：

[SQL]SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';

[Err] 错误: 操作符不存在: text ->unknown

LINE 1: SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';

^HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

以上，可以看到错误提示操作符不存在，因为第一步查询出来的是一个字符串，不是JSON对象。当然，我们可以改造下这个查询语句：

代码：

SELECT ('[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1)::JSON -> 'b';

结果：

2

以上，可以看到现在可以正确查询出结果，我们将第一步查询出来的字符串转成JSON对象，然后通过 Key 的方式来获取 Value。不过，这种查询方式相对于 -> 来说还是比较繁琐的。

2.2、 #> 和 #>> ：

在前一步，我们在一个JSON数组中可以使用 -> 下标值的方式来获取一个JSON对象。但是，如果我们我们检索的不是JSON数组，而是一个JSON对象中的JSON对象。很显然，这种下标获取的方式不再适用。不过，我们可以使用下面的方式来获取。

#> 表示获取指定路径的一个JSON对象，#>>表示获取指定路径的一个JSON对象的字符串。

代码：

SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{b}'结果：

{"ba":"b1","bb":"b2"}

以上，我们使用 #> 方式来获取一个JSON对象中的JSON对象。

注意：

在获取一个JSON对象时，除非是JSON数组中的下标，必须要要用 { } 将JSON对象的 Key 包裹起来，否则会抛出异常。

代码：

SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'结果：

[SQL]SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'[Err] 错误: 有缺陷的数组常量:"b"LINE 1: SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'

^DETAIL: 数组值必须以 "{" 或者维度信息开始。

同样的，我们还可以在 #> 的基础上，继续获取这个JSON对象内的相关信息。

代码：

SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'{b}'->'ba'结果：

"b1"

以上，可以看到 -> 获取的是一个JSON对象。

3. 操作函数
目前pgsql版本提供了两套函数分别处理，可以通用，名称也差不多，比如 json_each 和 jsonb_each ， json_array_elements 和 jsonb_array_elements 。

json相关的处理函数比较多，常用的有如下三个，这三个基本够用了

json_object_keys  // 返回json的键（多层只返回第一层），该函数不能用于纯数组.

json_array_elements  // 提取转换纯数组元素

json_extract_path   // 返回JSON值所指向的某个键元素（相当于 #> 操作符），该函数不能直接操作纯数组。
 

需要注意的是如果你创建字段用的是json就用json相关函数，如果创建字段用的是jsonb就用jsonb相关函数。

 

json_object_keys 函数示例：

select json_object_keys ('
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": "10"
}
')

输出：

ERROR:  cannot call json_object_keys on an array     // 不能用于数组

 

json_array_elements  函数 示例:

select json_array_elements ('
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"},
{"id": "111a13d3-0225-4431-b858-678c3cfea999", "weight": "3", "quantity": "11"}
]
')

我们看到json数据被分离成三条记录，这时我们就可以对其进行查询操作，

比如查询是否包含了weight=3的数据。

select * from json_array_elements ('
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"},
{"id": "111a13d3-0225-4431-b858-678c3cfea999", "weight": "3", "quantity": "11"}
]
') as jae
where jae::jsonb->>'weight' = '3' 

#输出：

我们看到这样就可以到对json数据内部进行查询了。

 

json_extract_path   函数示例：

比如要获取键 ‘goods’ 的值：

{
"goods":
[ 
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
], 
"quantity": {"max": "150", "min": "2"}
}

select json_extract_path   ('
{
"goods":
[ 
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
], 
"quantity": {"max": "150", "min": "2"}
}
' , 'goods' )  ;   // 第二个参数表示获取键为goods的值

#输出：

json_extract_path   函数和  pgsql  提供的操作符  #>  是一样的。

select ('
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
') ::json #> '{goods}'

两者的输出是一致的。

 

同样我们要输出 键quantity 下键max 的值：

select json_extract_path   ('
{
"goods":
[ 
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
], 
"quantity": {"max": "150", "min": "2"}
}
' , 'quantity','max' ) ;   

-- 或

select ('
{
"goods":
[ 
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
], 
"quantity": {"max": "150", "min": "2"}
}
') ::json #> '{quantity, max}'

两者输出是一样的。

 

这几个函数是可以联合使用的。

比如我们要查询 键“goods” 下weight =2 的id 和quantity 值，语句如下：

select jae::json->>'id' as id, jae::json->>'quantity' as quantity from json_array_elements (
json_extract_path ('
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
' , 'goods' ) ) as jae where jae::json->> 'weight' = '2'

#输出：

 

上述的json语句我们可以当做字段来使用，就相当于对表记录进行操作了。

 

接下来我们同个一个例子讲解json在表中的用法：

示例：查询表中jsonb_msg字段中goods下id=1003和1002的记录，表中输入如下图：

 

sql查询语句：

select name,* from upgrade_test.test1 test
where
( select count(*) from jsonb_array_elements (
jsonb_extract_path(test.json_msg , 'goods' ) ) as jae where jae::json->> 'id' in ('1001','1003') ) > 0 ; 

#输出：

 

效率还行：

Total query runtime: 11 msec
检索到 2 行。