更新时间:2024-06-03 GMT+08:00

JSON/JSONB函数和操作符

JSON/JSONB数据类型参考JSON/JSONB类型,操作符如表1表2所示。

表1 JSON/JSONB通用操作符

操作符

左操作数类型

右操作数类型

返回类型

描述

示例

->

Array-json(b)

int

json(b)

获得array-json元素。下标不存在返回空。

SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;
?column?
-------------
{"c":"baz"}
(1 row)

->

object-json(b)

text

json(b)

通过键获得值。不存在则返回空。

SELECT '{"a": {"b":"foo"}}'::json->'a';
?column?
-------------
{"b":"foo"}
(1 row)

->>

Array-json(b)

int

text

获得JSON 数组元素。下标不存在返回空。

SELECT '[1,2,3]'::json->>2;
?column?
----------
3
(1 row)

->>

object-json(b)

text

text

通过键获得值。不存在则返回空。

SELECT '{"a":1,"b":2}'::json->>'b';
?column?
----------
2
(1 row)

#>

container-json (b)

text[]

json(b)

获取在指定路径的JSON 对象,路径不存在则返回空。

SELECT '{"a": {"b":{"c": "foo"}}}'::json #>'{a,b}';
?column?
--------------
{"c": "foo"}
(1 row)

#>>

container-json (b)

text[]

text

获取在指定路径的JSON 对象,路径不存在则返回空。

SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json #>>'{a,2}';
?column?
----------
3
(1 row)

对于 #> 和 #>> 操作符,当给出的路径无法查找到数据时,不会报错,会返回空。

表2 JSONB额外支持操作符

操作符

右操作数类型

描述

例子

@>

jsonb

左边的JSON的顶层是否包含右边JSON的顶层所有项。

'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

<@

jsonb

左边的JSON的所有项是否全部存在于右边JSON的顶层。

'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb

?

text

键/元素的字符串是否存在于JSON 值的顶层。

'{"a":1, "b":2}'::jsonb ? 'b'

?|

text[]

这些数组字符串中的任何一个是否作为顶层键存在。

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']

?&

text[]

是否所有这些数组字符串都作为顶层键存在。

'["a", "b"]'::jsonb ?& array['a', 'b']

=

jsonb

判断两个jsonb的大小关系,同函数jsonb_eq。

/

<>

jsonb

判断两个jsonb的大小关系,同函数jsonb_ne。

/

<

jsonb

判断两个jsonb的大小关系,同函数jsonb_lt。

/

>

jsonb

判断两个jsonb的大小关系,同函数jsonb_gt。

/

<=

jsonb

判断两个jsonb的大小关系,同函数jsonb_le。

/

>=

jsonb

判断两个jsonb的大小关系,同函数jsonb_ge。

/

JSON/JSONB支持的函数

  • array_to_json(anyarray [, pretty_bool])

    描述:返回JSON类型的数组。将一个多维数组组成一个JSON数组。如果pretty_bool为true,将在一维元素之间添加换行符。

    返回类型:json

    示例:
    gaussdb=# SELECT array_to_json('{{1,5},{99,100}}'::int[]);
        array_to_json
        ------------------
        [[1,5],[99,100]]
        (1 row)
  • row_to_json(record [, pretty_bool])

    描述:返回JSON类型的行。如果pretty_bool为true,将在第一级元素之间添加换行符。

    返回类型:json

    示例:

    gaussdb=# SELECT row_to_json(row(1,'foo'));
         row_to_json     
    ---------------------
     {"f1":1,"f2":"foo"}
    (1 row)
  • json_array_element(array-json, integer)、jsonb_array_element(array-jsonb, integer)

    描述:同操作符`->`, 返回数组中指定下标的元素。

    返回类型:json、jsonb

    示例:
    gaussdb=# SELECT json_array_element('[1,true,[1,[2,3]],null]',2);
     json_array_element 
    --------------------
     [1,[2,3]]
    (1 row)
  • json_array_element_text(array-json, integer)、jsonb_array_element_text(array-jsonb, integer)

    描述:同操作符`->>`, 返回数组中指定下标的元素。

    返回类型:text、text

    示例:

    gaussdb=# SELECT json_array_element_text('[1,true,[1,[2,3]],null]',2);
    json_array_element_text
    -----------------------
      [1,[2,3]]
      (1 row)
  • json_object_field(object-json, text)、jsonb_object_field(object-jsonb, text)

    描述:同操作符`->`, 返回对象中指定键对应的值。

    返回类型:json、json

    示例:
     gaussdb=# SELECT json_object_field('{"a": {"b":"foo"}}','a');
      json_object_field
      -------------------
      {"b":"foo"}
      (1 row)
  • json_object_field_text(object-json, text)、jsonb_object_field_text(object-jsonb, text)

    描述:同操作符`->>`, 返回对象中指定键对应的值。

    返回类型:text、text

    示例:
     gaussdb=# SELECT json_object_field_text('{"a": {"b":"foo"}}','a');
      json_object_field_text
      ----------------------
      {"b":"foo"}
      (1 row)
  • json_extract_path(json, VARIADIC text[])、jsonb_extract_path((jsonb, VARIADIC text[])

    描述:等价于操作符`#>`。根据$2所指的路径,查找json,并返回。

    返回类型:json、jsonb

    示例:
     gaussdb=# SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
       json_extract_path
      -------------------
       "stringy"
      (1 row)
  • json_extract_path_op(json, text[])、jsonb_extract_path_op(jsonb, text[])

    描述:同操作符`#>`。根据$2所指的路径,查找json,并返回。

    返回类型:json、jsonb

    示例:
      gaussdb=# SELECT json_extract_path_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']);
       json_extract_path_op
      ---------------------
       "stringy"
      (1 row)
  • json_extract_path_text(json, VARIADIC text[])、jsonb_extract_path_text((jsonb, VARIADIC text[])

    描述:等价于操作符`#>>`。根据$2所指的路径,查找json,并返回。

    返回类型:text、text

    示例:
     gaussdb=# SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', 'f4','f6');
       json_extract_path_text
      -----------------------
       stringy
      (1 row)
  • json_extract_path_text_op(json, text[])、jsonb_extract_path_text_op(jsonb, text[])

    描述:同操作符`#>>`。根据$2所指的路径,查找json,并返回。

    返回类型:text、text

    示例:
     gaussdb=# SELECT json_extract_path_text_op('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}', ARRAY['f4','f6']);
       json_extract_path_text_op
      --------------------------
       stringy
      (1 row)
  • json_array_elements(array-json)、jsonb_array_elements(array-jsonb)

    描述:拆分数组,每一个元素返回一行。

    返回类型:json、jsonb

    示例:
     gaussdb=# SELECT json_array_elements('[1,true,[1,[2,3]],null]');
       json_array_elements
      ---------------------
       1
       true
       [1,[2,3]]
       null
      (4 rows)
  • json_array_elements_text(array-json)、jsonb_array_elements_text(array-jsonb)

    描述:拆分数组,每一个元素返回一行。

    返回类型:text、text

    示例:
    gaussdb=# SELECT * FROM  json_array_elements_text('[1,true,[1,[2,3]],null]');
         value
      -----------
       1
       true
       [1,[2,3]]
    
      (4 rows)
  • json_array_length(array-json)、jsonb_array_length(array-jsonb)

    描述:返回数组长度。

    返回类型:integer

    示例:
      gaussdb=# SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4,null]');
       json_array_length
      -------------------
                       6
      (1 row)
  • json_each(object-json)、jsonb_each(object-jsonb)

    描述:将对象的每个键值对拆分转换成一行两列。

    返回类型:setof(key text, value json)、setof(key text, value jsonb)

    示例:
     gaussdb=# SELECT * FROM  json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
       key |  value
      -----+----------
       f1  | [1,2,3]
       f2  | {"f3":1}
       f4  | null
      (3 rows)
  • json_each_text(object-json)、jsonb_each_text(object-jsonb)

    描述:将对象的每个键值对拆分转换成一行两列。

    返回类型:setof(key text, value text)、setof(key text, value text)

    示例:
     gaussdb=# SELECT * FROM  json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null}');
       key |  value
      -----+----------
       f1  | [1,2,3]
       f2  | {"f3":1}
       f4  |
      (3 rows)
  • json_object_keys(object-json)、jsonb_object_keys(object-jsonb)

    描述:返回对象中顶层的所有键。

    返回类型:SETOF text

    示例:
      gaussdb=# SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
       json_object_keys
      ------------------
       f1
       f2
       f1
      (3 rows)
  • jsonb中会有去重操作
      gaussdb=# SELECT jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}');
       jsonb_object_keys
      -------------------
       f1
       f2
      (2 rows)
  • json_populate_record(anyelement, object-json [, bool])、jsonb_populate_record(anyelement, object-jsonb [, bool])

    描述:$1必须是一个复合类型的参数。将会把object-json里的每个对键值进行拆分,以键当做列名,与$1中的列名进行匹配查找,并填充到$1的格式中。

    返回类型:anyelement、anyelement

    示例:
      gaussdb=# CREATE TYPE jpop AS (a text, b int, c bool);
      CREATE TYPE
      gaussdb=# SELECT * FROM json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}');
         a    | b | c
      --------+---+---
       blurfl |   |
      (1 row)
    gaussdb=# SELECT * FROM json_populate_record((1,1,null)::jpop,'{"a":"blurfl","x":43.2}');
         a    | b | c
      --------+---+---
       blurfl | 1 |
      (1 row)
    gaussdb=# DROP TYPE jpop;
    DROP TYPE
  • json_populate_record_set(anyelement, array-json [, bool])、jsonb_populate_record_set(anyelement, array-jsonb [, bool])

    描述:参考上述函数json_populate_record、jsonb_populate_record,对$2数组的每一个元素进行上述参数函数的操作,因此这也要求$2数组的每个元素都是object-json类型的。

    返回类型:setof anyelement、setof anyelement

    示例:
    gaussdb=# CREATE TYPE jpop AS (a text, b int, c bool);
      CREATE TYPE
    gaussdb=# SELECT * FROM json_populate_recordset(null::jpop, '[{"a":1,"b":2},{"a":3,"b":4}]');
       a | b | c
      ---+---+---
       1 | 2 |
       3 | 4 |
      (2 rows)
    gaussdb=# DROP TYPE jpop;
     DROP TYPE
  • json_typeof(json)、jsonb_typeof(jsonb)

    描述:检测json类型。

    返回类型:text、text

    示例:
     gaussdb=# SELECT value, json_typeof(value)  FROM (values (json '123.4'), (json '"foo"'), (json 'true'), (json 'null'), (json '[1, 2, 3]'), (json '{"x":"foo", "y":123}'), (NULL::json))  AS data(value);
              value         | json_typeof
      ----------------------+-------------
       123.4                | number
       "foo"                | string
       true                 | boolean
       null                 | null
       [1, 2, 3]            | array
       {"x":"foo", "y":123} | object
                            |
      (7 rows)
  • json_build_array( [VARIADIC "any"] )

    描述:从一个可变参数列表构造出一个JSON数组。

    返回类型:array-json

    示例:
     gaussdb=# SELECT json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}','');
                                  json_build_array                               
    -----------------------------------------------------------------------------
     ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}, null]
    (1 row)
  • json_build_object( [VARIADIC "any"] )

    描述:从一个可变参数列表构造出一个JSON对象,其入参必须为偶数个,两两一组组成键值对。注意键不可为null。

    返回类型:object-json

    示例:
     gaussdb=# SELECT json_build_object(1,2);
       json_build_object
      -------------------
       {"1" : 2}
      (1 row)
  • jsonb_build_object( [VARIADIC "any"] )

    描述:从一个可变参数列表构造出一个JSONB对象,其入参必须为偶数个,两两一组组成键值对。注意键值不可为NULL。

    返回类型:object-jsonb

    • 当可变参数列表的元素存在空字符串('')时,若数据库SQL兼容模式为A,则对应的元素位置返回结果为NULL;若数据库SQL兼容模式为PG, 则对应的元素位置返回结果为空字符串。这是因为A兼容模式时空字符串作为NULL处理。
    • 当可变参数列表的元素存在DATE类型时,若数据库SQL兼容模式为A,则对应的元素位置返回结果包含时分秒;若数据库SQL兼容模式为PG, 则对应的元素位置返回结果不包含时分秒。这是因为A兼容模式DATE会被替换为TIMESTAMP(0) WITHOUT TIME ZONE。

    示例:

    gaussdb=# SELECT jsonb_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
                               jsonb_build_object
    -------------------------------------------------------------------------
     {"a": 1, "b": 1.2, "c": true, "d": null, "e": {"x": 3, "y": [1, 2, 3]}}
    (1 row)
    
    gaussdb=# SELECT jsonb_build_object();
     jsonb_build_object
    --------------------
     {}
    (1 row)
  • json_to_record(object-json, bool)、json_to_record(object-json)

    描述:正如所有返回record 的函数一样,调用者必须用一个AS子句显式地定义记录的结构。会将object-json的键值对进行拆分重组,把键当做列名,去匹配填充AS显示指定的记录的结构。重载1的bool入参表示是否支持对象嵌套,true为支持,false为不支持,只有一个入参的时候,默认支持嵌套,且入参仅有一个时可将嵌套json对象转换为行类型及支持方括号格式数组的解析。对于时间类型date,ORA兼容模式下相当于timestamp(0),因此与PG兼容模式相比,在显示上会多出00:00:00时分秒字符串。

    返回类型:record

    示例:
    gaussdb=# SELECT * FROM json_to_record('{"a":1,"b":"foo","c":"bar"}',true) AS x(a int, b text, d text);
       a |  b  | d
      ---+-----+---
       1 | foo |
      (1 row)
    gaussdb=# SELECT * FROM json_to_record('{"a": {"x": 1, "y": 2},"b":"foo","c":[1, 2]}') AS x(a json, b text, c int[]);
              a         |  b  |   c   
      ------------------+-----+-------
       {"x": 1, "y": 2} | foo | {1,2}
      (1 row)
  • json_to_recordset(array-json, bool)

    描述:参考函数json_to_record,对数组内每个元素,执行上述函数的操作,因此这要求数组内的每个元素都得是object-json,bool也可参考函数json_to_record,true为支持解析嵌套对象,false为不支持,即是否支持json对象中元素的value值也是一个json对象。

    返回类型:set of record

    示例:
     gaussdb=# SELECT * FROM json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false) AS x(a int, b text, c boolean);
       a |  b  | c
      ---+-----+---
       1 | foo |
       2 | bar | t
      (2 rows)
  • json_object(text[])、json_object(text[], text[])

    描述:从一个文本数组构造一个object-json。这是个重载函数,当入参为一个文本数组的时候,其数组长度必须为偶数,数组内的成员被当作交替出现的键值对。两个文本数组的时候,第一个数组认为是键,第二个认为是值,两个数组长度必须相等。键值不可为NULL。

    返回类型:object-json

    示例:
    gaussdb=# SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}');
                            json_object
      -------------------------------------------------------
       {"a" : "1", "b" : "2", "3" : null, "d e f" : "a b c"}
      (1 row)
      gaussdb=# SELECT json_object('{a,b,"a b c"}', '{a,1,1}');
                    json_object
      ---------------------------------------
       {"a" : "a", "b" : "1", "a b c" : "1"}
      (1 row)
  • json_object([VARIADIC "any"] )

    描述:从一个可变参数列表构造出一个json对象,其入参必须为偶数个,两两一组组成键值对。键值为null报错;参数格式为奇数报错。

    参数:可变参数列表,入参是一个或多个成对的key-value组合。

    返回类型:json

    示例:
    gaussdb=# SELECT json_object('d',2,'c','name','b',true,'a',2,'a',NULL,'d',1);
                   json_object                
    ------------------------------------------
     {"a": 2, "b": true, "c": "name", "d": 2}
    (1 row)
    
    gaussdb=# SELECT json_object('d',2,true,'name','b',true,'a',2,'aa', current_timestamp);
                                     json_object                                  
    ------------------------------------------------------------------------------
     {"1": "name", "a": 2, "b": true, "d": 2, "aa": "2023-08-12 11:28:13.385958"}
    (1 row)

    此函数在MYSQL模式数据库中,且GUC参数 b_format_version = '5.7'和b_format_dev_version = 's1'时生效,生效时会替换原有json_object行为。

  • json_append/json_array_append(json, [VARIADIC "any"])

    描述:接受一个json入参和一个可变参数列表。从一个可变参数列表构造出若干对json_path和value对,向json类型中指定的路径追加一个值,并返回修改后的json。json_append与json_array_append完全一致。如果有任何参数为null返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:第一个入参必须为符合格式的json,第二个入参为可变的参数列表,json_path-value对将从可变参数列表中构造。详情请参见表3

    表3 参数说明

    参数名称

    类型

    描述

    取值范围

    json

    json

    需要被修改的json值。

    符合json格式。

    [VARIADIC "any"]

    可变any数组

    可变参数列表,包含可变个json_path-value对。

    json_path必须是一个有效的路径表达式,value可以是任意值。

    返回类型:json

    示例:
    gaussdb=# SELECT json_array_append('[1, [2, 3]]', '$[1]', 4, '$[0]', false, '$[0]', null, '$[0]', current_timestamp);
                           json_array_append                        
    ----------------------------------------------------------------
     [[1, false, null, "2023-08-12 14:27:16.142355+08"], [2, 3, 4]]
    (1 row)
  • json_array([VARIADIC "any"])

    描述:从一个可变参数列表构造出一个数组,并返回数组形式的json。如果该函数没有任何参数,则返回一个空的数组形式的json。

    参数:入参为可变参数列表,这些列表中的值可以是任意类型。

    返回类型:json

    示例:
    --不输入入参,返回一个空的数组形式的json
    gaussdb=# SELECT json_array();
     json_array 
    ------------
     []
    (1 row)
    
    --入参可以是任意类型
    gaussdb=# SELECT json_array(TRUE, FALSE, NULL, 114, 'text', current_timestamp);
                                json_array                             
    -------------------------------------------------------------------
     [true, false, null, 114, "text", "2023-08-12 15:17:34.979294+08"]
    (1 row)
    
  • json_array_insert(json, [VARIADIC "any"])

    描述:从一个可变参数列表中,构造一个或者多个json_path和value的路径-值对,向json中的json_path指定数组路径插入一个值value并返回新的json。如果指定的路径上有值,则在该路径上插入,并将已存在的值后移。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表4 参数说明所示。

    表4 参数说明

    参数名称

    类型

    描述

    取值范围

    json

    json

    需要修改的json值。

    必须满足json的格式。

    [VARIADIC "any"]

    可变any数组

    可变参数列表,可以支持一个或多个json_path-value对。

    json_path必须是一个有效的路径表达式,value可以是任意值。

    返回类型:json

    示例:
    --一对json_path-value用例
    gaussdb=# SELECT json_array_insert('[1, [2, 3]]', '$[1]', 4);
     json_array_insert 
    -------------------
     [1, 4, [2, 3]]
    (1 row)
    
    --多个json_path-value用例
    gaussdb=# SELECT json_array_insert('{"x": 1, "y": [1, 2]}', '$.y[0]', NULL, '$.y[0]', 123, '$.y[3]', current_timestamp);
                            json_array_insert                         
    ------------------------------------------------------------------
     {"x": 1, "y": [123, null, 1, "2023-08-14 14:54:12.85087+08", 2]}
    (1 row)
  • json_contains(target_json, candidate_json[, json_path])

    描述:入参为两个json对象和一个可选的json_path路径。如果不选定json路径,则判断target_json是否包含了candidate_json。如果选定路径,则在target_json指定路径json_path指向的json中,判断是否包含candidate_json。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表5所示。

    表5 参数说明

    参数名称

    类型

    描述

    取值范围

    target_json

    json

    目标json对象,需要判断candidate_json是否被该json包含。

    必须满足json的格式。

    candidate_json

    json

    json子对象,需要判断是否被target_json包含。

    必须满足json的格式。

    json_path

    text

    可选参数,指定json的路径json_path。如果指定,则使用target_json的json_path指定路径判断包含关系。

    json_path必须是一个有效的路径表达式。

    返回类型:bigint类型,如果包含返回1,如果不包含返回0。

    示例:
    --不指定路径
    gaussdb=# SELECT json_contains('[1, 2, {"x": 3}]', '{"x":3}');
     json_contains 
    ---------------
                 1
    (1 row)
    
    --指定路径
    gaussdb=# SELECT json_contains('[1, 2, {"x": 3},[1,2,3,4]]', '2','$[1]');
     json_contains 
    ---------------
                 1
    (1 row)
    
    gaussdb=# SELECT json_contains('[1, 2, {"x": 3},[1,2,3,4]]', '1','$[1]');
     json_contains 
    ---------------
                 0
    (1 row)
  • json_contains_path(json, mode_str, [VARIADIC text])

    描述:该函数用于检查一个json对象在指定的路径上是否有值,这个路径可以是多个。第一个入参为json对象,第二个入参为字符串“one”或“all”,它指示是否检查所有的路径。第三个入参为一个可变参数列表,所有的json_path路径将会从这个可变参数列表中构造。如果路径根据模式判断存在返回1,如果不存在返回0。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式则会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表6所示。

    表6 参数说明

    参数名称

    类型

    描述

    取值范围

    json

    json

    需要传入的json对象。

    必须满足json的格式。

    mode_str

    text

    字符串“one”或者 “all”,如果是“one”,其中一个路径存在返回1,否则返回0;如果是“all”,所有路径存在返回1,否则返回0。

    必须为字符串“one”或“all”,不区分大小写。

    [VARIADIC text]

    可变text数组

    可变参数列表,从此列表中构造所有的json_path路径。

    json_path必须为有效的路径表达式。

    返回类型:bigint类型

    示例:
    --all场景,需要所有路径都存在
    gaussdb=# SELECT json_contains_path('[1, 2, {"x": 3}]', 'all', '$[0]', '$[1]', '$[2]');
     json_contains_path 
    --------------------
                      1
    (1 row)
    
    gaussdb=# SELECT json_contains_path('[1, 2, {"x": 3}]', 'all', '$[0]', '$[1]', '$[6]');
     json_contains_path 
    --------------------
                      0
    (1 row)
    
    --one场景,其中一个路径存在即可
    gaussdb=# SELECT json_contains_path('[1, 2, {"x": 3}]', 'one', '$[0]', '$[1]', '$[5]');
     json_contains_path 
    --------------------
                      1
    (1 row)
  • json_depth(json)

    描述:入参为一个json对象,此函数用于返回传入json对象的最大深度。入参为null,返回null。

    参数:入参为一个需要查询深度的json。入参如果不是有效的json则报错。

    返回类型:int

    示例:
    --空数组的深度为1
    gaussdb=# SELECT json_depth('[]');
     json_depth 
    ------------
              1
    (1 row)
    
    gaussdb=# SELECT json_depth('{"s":1, "x":2,"y":[1]}');
     json_depth 
    ------------
              3
    (1 row)
  • json_extract(json, [VARIADIC text])

    描述:入参为一个json对象,和若干个json_path路径,json路径从可变参数列表中构造,此函数在json中提取指定json_path路径的数据,并将其组合成一个json数组返回。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式则会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表7所示。

    表7 参数说明

    参数名称

    类型

    描述

    取值范围

    json

    json

    需要提取路径的json值。

    必须满足json的格式。

    [VARIADIC text]

    可变text数组

    可变参数列表,可以支持一个或多个json_path路径。

    json_path必须是一个有效的路径表达式。

    返回类型:json

    示例:
    --提取一个路径的值
    gaussdb=# SELECT json_extract('[1, 2, {"x": 3}]', '$[2]');
     json_extract 
    --------------
     {"x": 3}
    (1 row)
    
    --提取多个路径的值
    gaussdb=# SELECT json_extract('["a", ["b", "c"], "d"]', '$[1]', '$[2]', '$[3]');
       json_extract    
    -------------------
     [["b", "c"], "d"]
    (1 row)
  • json_insert(json, [VARIADIC any])

    描述:入参为一个json对象,若干个json_path路径和需要插入的值,json_path和值必须两两配对,此函数在json中的json_path路径指定的位置插入一个value值,此函数只能将数据插入到不存在路径。如果json中已经存在指定的路径,则不会插入数据。json和json_path中存在null值返回null。并且json_path中不能存在“*”、“**”通配符,否则报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表8所示。

    表8 参数说明

    参数名称

    类型

    描述

    取值范围

    json

    json

    需要被插入值的json值。

    必须满足json的格式。

    [VARIADIC any]

    可变any数组

    可变参数列表,可以支持一个或多个json_path-value对。

    json_path必须是一个有效的路径表达式,value可以是任意值。

    返回类型:json

    示例:
    gaussdb=# SELECT json_insert('[1, [2, 3], {"a": [4, 5]}]', '$[3]', 2);
              json_insert          
    -------------------------------
     [1, [2, 3], {"a": [4, 5]}, 2]
    (1 row)
    
    gaussdb=# SELECT json_insert('[1, [2, 3], {"a": [4, 5]}]', '$[10]', 10,'$[5]', 5);
                json_insert            
    -----------------------------------
     [1, [2, 3], {"a": [4, 5]}, 10, 5]
    (1 row)
  • json_keys(json[, json_path])

    描述:入参为一个json对象,和一个可选的json_path路径。如果没有传入json_path路径,此函数返回json对象中最上层对象的成员key值的json数组。如果传入了json_path路径,此函数返回该路径对应的json对象中最上层的成员key值的json数组。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。

    参数:如表9 参数说明所示。

    表9 参数说明

    参数名称

    类型

    描述

    取值范围

    json

    json

    一个json值。

    必须满足json的格式。

    json_path

    text

    可选的,一个json_path路径。

    json_path必须是一个有效的路径表达式。

    返回类型:json

    示例:
    gaussdb=# SELECT json_keys('{"x": 1, "y": 2, "z": 3}');
        json_keys    
    -----------------
     ["x", "y", "z"]
    (1 row)
    
    gaussdb=# SELECT json_keys('[1,2,3,{"name":"Tom"}]','$[3]');
     json_keys 
    -----------
     ["name"]
    (1 row)
  • json_length(json[, json_path])

    描述:入参为一个json对象,和一个可选的json_path路径。如果没有传入json_path路径,此函数返回传入json对象的长度。如果传入了json_path路径,此函数返回该路径对应的json对象的长度。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。

    参数:如表10所示。

    表10 参数说明

    参数名称

    类型

    描述

    取值范围

    json

    json

    一个json值。

    必须满足json的格式。

    json_path

    text

    可选的,一个json_path路径

    json_path必须是一个有效的路径表达式。

    返回类型:int

    示例:
    gaussdb=# SELECT json_length('[1,2,3,4,5]');
     json_length 
    -------------
               5
    (1 row)
    
    gaussdb=# SELECT json_length('{"name":"Tom", "age":24, "like":"football"}');
     json_length 
    -------------
               3
    (1 row)
  • json_merge([VARIADIC any])

    描述:入参为多个json类型,入参的个数大于等于2,json对象从可变参数列表中构造;此函数将所有传入的json进行合并,并返回合并后的结果。入参中有null,返回null。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:可变参数列表,多个json对象会从这个列表中构造。

    返回类型:json

    示例:
    gaussdb=# SELECT json_merge('[1, 2]','[2]');
     json_merge 
    ------------
     [1, 2, 2]
    (1 row)
    
    gaussdb=# SELECT json_merge('{"b":"2"}','{"a":"1"}','[1,2]');
              json_merge          
    ------------------------------
     [{"a": "1", "b": "2"}, 1, 2]
    (1 row)
  • json_quote(text)

    描述:入参为一个text类型,此函数使用双引号将入参包装成一个字符串形式的json类型并返回。

    参数:入参为需要被包装的字符串。

    返回类型:json

    示例:
    gaussdb=# SELECT json_quote('gauss');
     json_quote 
    ------------
     "gauss"
    (1 row)
  • json_unquote(json)

    描述:入参为一个json,此函数将取消入参的双引号引用,并返回取消后的字符串。

    参数:一个json值。

    返回类型:text

    示例:
    gaussdb=# SELECT json_unquote('"gauss"');
     json_unquote 
    --------------
     gauss
    (1 row)
  • json_remove(json, [VARIADIC text])

    描述:入参为一个json对象,和若干个需要被删除的json_path路径,这些json_path路径从可变参数列表中构造。此函数将删除json中指定json_path路径的值,并返回删除后的json。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表11所示。

    表11 参数说明

    参数说明

    类型

    描述

    取值范围

    json

    json

    一个json值。

    必须满足json的格式。

    [VARIADIC text]

    可变text数组

    可变参数列表,可以支持一个或多个json_path路径。

    json_path必须是一个有效的路径表达式。

    返回类型:json

    示例:
    gaussdb=# SELECT json_remove('[0, 1, 2, [3, 4]]', '$[0]', '$[2]');
     json_remove 
    -------------
     [1, 2]
    (1 row)
    
    gaussdb=# SELECT json_remove('[0, 1, 2, [3, 4]]', '$[0]', '$[0]','$[0]');
     json_remove 
    -------------
     [[3, 4]]
    (1 row)
  • json_replace(json, [VARIADIC any])

    描述:入参为一个json对象,和若干个json_path路径和需要替换的值,json_path和值必须两两配对;此函数在一个json中使用指定的值来替换指定json_path路径上的数据,并返回修改后的json。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表12所示。

    表12 参数说明

    参数说明

    类型

    描述

    取值范围

    json

    json

    一个json值。

    必须满足json的格式。

    [VARIADIC any]

    可变any入参

    可变参数列表,可以支持一个或多个json_path-value对。

    json_path必须是一个有效的路径表达式,value可以是任意值。

    返回类型:json

    示例:
    gaussdb=# SELECT json_replace('{"x": 1}', '$.x', 'true');
     json_replace  
    ---------------
     {"x": "true"}
    (1 row)
    
    gaussdb=# SELECT json_replace('{"x": 1}', '$.x', true, '$.x', 123, '$.x', 'asd', '$.x', null);
     json_replace 
    --------------
     {"x": null}
    (1 row)
  • json_search(json, mode_str, search_str, escape_char, json_path)

    描述:函数返回一个给定字符串在一个 json中的路径。它返回一个路径字符串或者由多个路径组成的数组。如果参数json、search_str或json_path任意一个是null,返回null。如果json格式错误或者json_path不是有效的路径表达式则会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表13 参数说明所示。

    表13 参数说明

    参数说明

    类型

    描述

    取值范围

    json

    json

    目标的json。

    必须满足json的格式。

    mode_str

    text

    字符串“one”或者 “all”,如果是“one”,表示只获取第一个匹配到的路径;如果是“all”,则会匹配所有符合的路径。

    必须是字符串“one”或者“all”,不区分大小写。

    search_str

    text

    被搜索的字符串,正常情况下为全字匹配,但是可以使用通配符:%: 匹配任意数量的任意字符,_: 匹配一个任意字符。

    -

    escape_char

    text

    可选的,如果search_str中包含通配符,则该参数定义为通配符的转义字符。默认为'\'。若在通配符前加上该字符,则不将该字符看做通配符。

    可以是任意单个字符。

    json_path

    text

    可选的,若指定json_path,则在该路径下搜索。

    json_path必须是一个有效的路径表达式。

    返回类型:text

    示例:
    --all模式
    gaussdb=# SELECT json_search('{"a":"abc","b":"abc"}','all','abc');
      json_search   
    ----------------
     ["$.a", "$.b"]
    (1 row)
    
    --one模式
    gaussdb=# SELECT json_search('{"a":"abc","b":"abc"}','one','abc');
     json_search 
    -------------
     "$.a"
    (1 row)
    
    --使用默认转义字符
    gaussdb=# SELECT json_search('{"a":"abc","b":"a%c"}','one','a\%c');
     json_search 
    -------------
     "$.b"
    (1 row)
    
  • json_set(json, [VARIADIC any])

    描述:入参为一个json对象,若干个json_path路径和需要设置的值,json_path和值必须两两配对;此函数在一个json中使用指定的值来更新指定json_path路径上的数据,并返回修改后的json;如果指定的路径不存在值,则在对应的路径上插入值。json和json_path路径中有null值返回null,如果json格式错误或者json_path不是有效的路径表达式或者json_path包含“*”、“**”会报错。如果有null值和格式错误场景同时在可变参数列表中,则按异常的先后顺序处理,先有null值返回null,先有报错则报错。

    参数:如表14 参数说明所示。

    表14 参数说明

    参数说明

    类型

    描述

    取值范围

    json

    json

    一个json值。

    必须满足json的格式。

    [VARIADIC any]

    可变any数组

    可变参数列表,可以支持一个或多个json_path-value对。

    json_path必须是一个有效的路径表达式,value可以是任意值。

    返回类型:json

    示例:
    gaussdb=# SELECT json_set('{"s":3}','$.s','d');
      json_set  
    ------------
     {"s": "d"}
    (1 row)
    
    gaussdb=# SELECT json_set('{"s":3}','$.a','d','$.a','1');
          json_set      
    --------------------
     {"a": "1", "s": 3}
    (1 row)
  • json_type(json)

    描述:入参为一个json对象,函数返回一个字符串,它代表了给定的 json值的类型。入参为null返回null。

    参数:一个json值。详情请参见表15

    返回类型:text

    表15 json_type返回值

    入参类型

    返回值

    数组类型的json值

    ARRAY

    对象类型的json值

    OBJECT

    字符串类型的json值

    STRING

    数值类型的json值

    NUMBER

    bool类型的json值

    BOOLEAN

    NULL值

    NULL

    示例:
    gaussdb=# SELECT json_type('{"w":{"2":3},"2":4}');
     json_type 
    -----------
     OBJECT
    (1 row)
    
    gaussdb=# SELECT json_type('[1,2,2,3,3,4,4,4,4,4,4,4,4]');
     json_type 
    -----------
     ARRAY
    (1 row)
  • json_valid(json)

    描述:函数返回 0 和 1 来指示给定的参数是否是一个有效的json。入参为null返回null。

    参数:一个json值。

    返回类型:bigint

    示例:
    gaussdb=# SELECT json_valid('{"name":"Tom"}');
     json_valid 
    ------------
              1
    (1 row)
    
    
    gaussdb=# SELECT json_valid('[1,23,4,5,5]');
     json_valid 
    ------------
              1
    (1 row)
    
    gaussdb=# SELECT json_valid('[1,23,4,5,5]}');
     json_valid 
    ------------
              0
    (1 row)
  • json_agg(any)

    描述:将值聚集为json数组。

    返回类型:array-json

    示例:
    gaussdb=# CREATE TABLE classes(name varchar, score int);
    CREATE TABLE
    gaussdb=# INSERT INTO classes VALUES('A',2);
    INSERT 0 1
    gaussdb=# INSERT INTO classes VALUES('A',3);
    INSERT 0 1
    gaussdb=# INSERT INTO classes VALUES('D',5);
    INSERT 0 1
    gaussdb=# INSERT INTO classes VALUES('D',null);
    INSERT 0 1
    gaussdb=# SELECT * FROM classes;
     name | score 
    ------+-------
     A    |     2
     A    |     3
     D    |     5
     D    |      
    (4 rows)
    
    gaussdb=# SELECT name, json_agg(score) score FROM classes GROUP BY name ORDER BY name;
     name |   score   
    ------+-----------
     A    | [2, 3]
     D    | [5, null]
    (2 rows)
    
    gaussdb=# DROP TABLE classes;
    DROP TABLE
  • json_object_agg(any, any)

    描述:将值聚集为json对象。

    返回类型:object-json

    示例:
    gaussdb=# CREATE TABLE classes(name varchar, score int);
    CREATE TABLE
    gaussdb=# INSERT INTO classes VALUES('A',2);
    INSERT 0 1
    gaussdb=# INSERT INTO classes VALUES('A',3);
    INSERT 0 1
    gaussdb=# INSERT INTO classes VALUES('D',5);
    INSERT 0 1
    gaussdb=# INSERT INTO classes VALUES('D',null);
    INSERT 0 1
    gaussdb=# SELECT * FROM classes;
     name | score 
    ------+-------
     A    |     2
     A    |     3
     D    |     5
     D    |      
    (4 rows)
    
    gaussdb=# SELECT json_object_agg(name, score) FROM classes GROUP BY name ORDER BY name;
         json_object_agg     
    -------------------------
     { "A" : 2, "A" : 3 }
     { "D" : 5, "D" : null }
    (2 rows)
    
    gaussdb=# DROP TABLE classes;
    DROP TABLE
  • jsonb_contained(jsonb, jsonb)

    描述:同操作符 `<@`, 判断$1中的所有元素是否在$2的顶层存在。

    返回类型:bool

    示例:
     gaussdb=# SELECT jsonb_contained('[1,2,3]', '[1,2,3,4]');
       jsonb_contained
      -----------------
       t
      (1 row)
  • jsonb_contains(jsonb, jsonb)

    描述:同操作符 `@>`, 判断$1中的顶层所有元素是否包含在$2的所有元素。

    返回类型:bool

    示例:
     gaussdb=# SELECT jsonb_contains('[1,2,3,4]', '[1,2,3]');
       jsonb_contains
      ----------------
       t
      (1 row)
  • jsonb_exists(jsonb, text)

    描述:同操作符 `?`, 字符串$2是否存在$1的顶层以key\elem\scalar的形式存在。

    返回类型:bool

    示例:
      gaussdb=# SELECT jsonb_exists('["1",2,3]', '1');
       jsonb_exists
      --------------
       t
      (1 row)
  • jsonb_exists_all(jsonb, text[])

    描述:同操作符 `?&`, 字符串数组$2里面,是否所有的元素,都在$1的顶层以key\elem\scalar的形式存在。

    返回类型:bool

    示例:
      gaussdb=# SELECT jsonb_exists_all('["1","2",3]', '{1, 2}');
       jsonb_exists_all
      ------------------
       t
      (1 row)
  • jsonb_exists_any(jsonb, text[])

    描述:同操作符 `?|`, 字符串数组$2里面,是否存在的元素,在$1的顶层以key\elem\scalar的形式存在。

    返回类型:bool

    示例:
      gaussdb=# SELECT jsonb_exists_any('["1","2",3]', '{1, 2, 4}');
       jsonb_exists_any
      ------------------
       t
      (1 row)
  • jsonb_cmp(jsonb, jsonb)

    描述:比较大小,正数代表大于,负数代表小于,0表示相等。

    返回类型:integer

    示例:
    gaussdb=# SELECT jsonb_cmp('["a", "b"]', '{"a":1, "b":2}');
       jsonb_cmp
      -----------
              -1
      (1 row)
  • jsonb_eq(jsonb, jsonb)

    描述:同操作符 `=`, 比较两个值的大小。

    返回类型:bool

    示例:
      gaussdb=# SELECT jsonb_eq('["a", "b"]', '{"a":1, "b":2}');
       jsonb_eq
      ----------
       f
      (1 row)
  • jsonb_ne(jsonb, jsonb)

    描述:同操作符 `<>`, 比较两个值的大小。

    返回类型:bool

    示例:
      gaussdb=# SELECT jsonb_ne('["a", "b"]', '{"a":1, "b":2}');
       jsonb_ne
      ----------
       t
      (1 row)
  • jsonb_gt(jsonb, jsonb)

    描述:同操作符 `>`, 比较两个值的大小。

    返回类型:bool

    示例:

     gaussdb=# SELECT jsonb_gt('["a", "b"]', '{"a":1, "b":2}');
       jsonb_gt
      ----------
       f
      (1 row)
  • jsonb_ge(jsonb, jsonb)

    描述:同操作符 `>=`, 比较两个值的大小。

    返回类型:bool

    示例:
     gaussdb=# SELECT jsonb_ge('["a", "b"]', '{"a":1, "b":2}');
       jsonb_ge
      ----------
       f
      (1 row)
  • jsonb_lt(jsonb, jsonb)

    描述:同操作符 `<`, 比较两个值的大小。

    返回类型:bool

    示例:
     gaussdb=# SELECT jsonb_lt('["a", "b"]', '{"a":1, "b":2}');
       jsonb_lt
      ----------
       t
      (1 row)
  • jsonb_le(jsonb, jsonb)

    描述:同操作符 `<=`, 比较两个值的大小。

    返回类型:bool

    示例:
     gaussdb=# SELECT jsonb_le('["a", "b"]', '{"a":1, "b":2}');
       jsonb_le
      ----------
       t
      (1 row)
  • to_json(anyelement)

    描述:把参数转换为`json`

    返回类型:json

    示例:
     gaussdb=# SELECT to_json('{1,5}'::text[]);
        to_json
      -----------
       ["1","5"]
      (1 row)
  • to_jsonb(anyelement)

    描述:把输入的anyelement参数转换为`jsonb`类型。

    返回类型:jsonb

    • 当参数为空字符串('')时,若数据库SQL兼容模式为A,则返回结果为NULL;若数据库SQL兼容模式为PG, 则返回结果为空字符串。这是因为A兼容模式时空字符串作为NULL处理。
    • 当参数为DATE类型时,若数据库SQL兼容模式为A,则返回结果包含时分秒;若数据库SQL兼容模式为PG, 则返回结果不包含时分秒。这是因为A兼容模式DATE会被替换为TIMESTAMP(0) WITHOUT TIME ZONE。
    示例:
    gaussdb=# SELECT to_jsonb(ARRAY[1, 2, 3, 4]);
       to_jsonb
    --------------
     [1, 2, 3, 4]
    (1 row)
  • jsonb_hash(jsonb)

    描述:对jsonb进行hash运算。

    返回类型:integer

    示例:

     gaussdb=# SELECT jsonb_hash('[1,2,3]');
     jsonb_hash
    ------------
     -559968547
    (1 row)
  • 其他函数
     json_agg_transfn
     json_agg_finalfn
     json_object_agg_transfn
     json_object_agg_finalfn