PostgreSQL 9.5以上的版本中有了很多方便的操作符,使得操作 JSON 变得非常方便了。

 

一、 -> 和 ->> :

-> 表示获取一个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。不过,这种查询方式相对于 -> 来说还是比较繁琐的。

 

二、 #> 和 #>> :

在前一步,我们在一个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对象。

有事请百度。