JSON processing functions

Learn about the functions that are available for processing json and jsonb values.

jsonb_array_length

Return type: int

Returns the number of elements in the outermost JSON array.

Examples:

SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
 JSONB_ARRAY_LENGTH 
--------------------
                  5
(1 row)

SELECT jsonb_array_length('["一",2,3,{"二":1,"三":[5,6]},4, "四"]');
 JSONB_ARRAY_LENGTH 
--------------------
                  6
(1 row)

SELECT jsonb_array_length('{"f1":1,"f2":[5,6]}');
ERROR:  cannot get array length of a non-array

SELECT jsonb_array_length('4');
ERROR:  cannot get array length of a scalar

json_extract_path | jsonb_extract_path

json_extract_path(from_json json, path_elems jsonb array)

jsonb_extract_path(from_json jsonb, path_elems jsonb array)

Return type: json, jsonb

Returns JSON value pointed to by path_elems. Equivalent to the #> operator.

Examples:

SELECT json_extract_path('["a", "foo", "baz"]', '[0]');
 JSON_EXTRACT_PATH 
-------------------
 "a"
(1 row)

SELECT json_extract_path('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]', '[0]');
 JSON_EXTRACT_PATH 
-------------------
 {"a": "foo"}
(1 row)

SELECT jsonb_extract_path('["a", "foo", "baz"]', '[0]');
 JSONB_EXTRACT_PATH 
--------------------
 "a"
(1 row)

SELECT jsonb_extract_path('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]', '[0,"a"]');
 JSONB_EXTRACT_PATH 
--------------------
 "foo"
(1 row)

json_extract_path_text(from_json json, VARIADIC path_elems jsonb array) | jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems jsonb array)

Return type: text

Returns JSON value pointed to by path_elems as text. Equivalent to the #>> operator.

Examples:

SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','["f4","f6"]');
 JSON_EXTRACT_PATH_TEXT 
------------------------
 stringy
(1 row)

SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}','["f2"]');
 JSON_EXTRACT_PATH_TEXT 
------------------------
 {"f3": 1}
(1 row)

SELECT jsonb_extract_path('["a", "foo", "baz"]', '[0]');
 JSONB_EXTRACT_PATH 
--------------------
 "a"
(1 row)

SELECT jsonb_extract_path('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]', '[0]');
 JSONB_EXTRACT_PATH 
--------------------
 {"a": "foo"}
(1 row)

jsonb_typeof(jsonb)

Return type: text

Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

Examples:

SELECT jsonb_typeof('{}') AS object;
 OBJECT 
--------
 object
(1 row)

SELECT jsonb_typeof('[]') AS array;
 ARRAY 
-------
 array
(1 row)

SELECT jsonb_typeof('null') AS "null";
 null 
------
 null
(1 row)

SELECT jsonb_typeof('1') AS number;
 NUMBER 
--------
 number
(1 row)

jsonb_strip_nulls(from_json jsonb)

Return type: jsonb

Returns from_json with all object fields that have null values omitted. Other null values are untouched.

If the argument to json_strip_nulls contains duplicate field names in any object, the result could be semantically somewhat different, depending on the order in which they occur. This is not an issue for jsonb_strip_nulls since jsonb values never have duplicate object field names.

Examples:

SELECT jsonb_strip_nulls('[1,2,null,3,4]');
 JSONB_STRIP_NULLS  
--------------------
 [1, 2, null, 3, 4]
(1 row)

SELECT jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
    JSONB_STRIP_NULLS     
--------------------------
 [1, {"a": 1, "c": 2}, 3]
(1 row)

SELECT jsonb_strip_nulls('{"空值": {"空值 1": null, "空值 2": null}, "没有": {} }');
    JSONB_STRIP_NULLS     
--------------------------
 {"没有": {}, "空值": {}}
(1 row)

SELECT jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
 JSONB_STRIP_NULLS  
--------------------
 {"a": {}, "d": {}}
(1 row)

jsonb_set(target jsonb, path jsonb array, new_value jsonb [, create_missing boolean])

Return type: jsonb

Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist. As with the path oriented operators, negative integers that appear in path count from the end of JSON arrays.

Examples:

SELECT jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '["n"]'    , '[1,2,3]');
                                JSONB_SET                                 
--------------------------------------------------------------------------
 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": [1, 2, 3]}
(1 row)

SELECT jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '["b",-1]' , '[1,2,3]');
                                  JSONB_SET                                  
-----------------------------------------------------------------------------
 {"a": 1, "b": [1, [1, 2, 3]], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)

SELECT jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '["d",1,0]', '[1,2,3]');
                                  JSONB_SET                                  
-----------------------------------------------------------------------------
 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [[1, 2, 3], 3]}, "n": null}
(1 row)

SELECT jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '["n"]'    , '{"1": 2}');
                                JSONB_SET                                
-------------------------------------------------------------------------
 {"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": {"1": 2}}
(1 row)

jsonb_insert(target jsonb, path jsonb array, new_value jsonb [, insert_after boolean])

Return type: jsonb

Returns target with new_value inserted. If target section designated by path is in a JSONB array, new_value will be inserted before target or after if insert_after is true (default is false). If target section designated by path is in JSONB object, new_value will be inserted only if target does not exist. As with the path oriented operators, negative integers that appear in path count from the end of JSON arrays.

Examples:

SELECT jsonb_insert('{"a": [0,1,2]}', '["a", 1]', '"new_value"');
         JSONB_INSERT          
-------------------------------
 {"a": [0, "new_value", 1, 2]}
(1 row)

SELECT jsonb_insert('{"a": [0,1,2]}', '["a", 1]', '"new_value"', true);
         JSONB_INSERT          
-------------------------------
 {"a": [0, 1, "new_value", 2]}
(1 row)

SELECT jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '["a", "b", "c", 2]', '"new_value"');
                        JSONB_INSERT                        
------------------------------------------------------------
 {"a": {"b": {"c": [0, 1, "new_value", "test1", "test2"]}}}
(1 row)

SELECT jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '["a", "b", "c", 2]', '"new_value"', true);
                        JSONB_INSERT                        
------------------------------------------------------------
 {"a": {"b": {"c": [0, 1, "test1", "new_value", "test2"]}}}
(1 row)

jsonb_pretty(from_json jsonb)

Return type: text

Returns from_json as indented JSON text.

Examples:

SELECT jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
                                                                                      JSONB_PRETTY                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {
    "a": "test",
    "b": [
        1,
        2,
        3
    ],
    "c": "test3",
    "d": {
        "dd": "test4",
        "dd2": {
            "ddd": "test5"
        }
    }
}
(1 row)

SELECT jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');
                                                                                                JSONB_PRETTY                                                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    [
        [
            {
                "x": true
            },
            6,
            7
        ],
        8
    ],
    3
]
(1 row)

SELECT jsonb_pretty('{"a":["b", "c"], "d": {"e":"f"}}');
                                   JSONB_PRETTY                                   
----------------------------------------------------------------------------------
 {
    "a": [
        "b",
        "c"
    ],
    "d": {
        "e": "f"
    }
}
(1 row)

SELECT jsonb_pretty('{"数":["二十七", "二十八"], "雇员": {"职称":"电气工程师"}}');
                                                JSONB_PRETTY                                                
------------------------------------------------------------------------------------------------------------
 {
    "数": [
        "二十七",
        "二十八"
    ],
    "雇员": {
        "职称": "电气工程师"
    }
}
(1 row)