Preface
Support for JSON types was introduced in PostgreSQL 9.2. After several major versions of evolution, the support for JSON number types has been relatively complete. CRUD operations on JSON format information in PG and indexing specific nodes can be easily implemented. This article uses PostgreSQL version 15.3 as a demonstration, and I hope you can gain something.
Two data types json and jsonb
Two different data types are provided in PG, namely JSON and JSONB. As the name implies, JSON is the original format for storing strings, while JSONB is the binary encoded version. JSON needs to store original formats including spaces, so there will be a parsing process every time you query. However, JSONB query does not require real-time parsing, so it is more efficient.
In short, for accurate storage, JSON is inserted fast and query is slow; for efficient query, JSONB is inserted slow and search is fast.
If there is no special reason, it is best to use the JSONB type.
json and jsonb operators
| Operator | Right operand type | describe | example | Example results |
|---|---|---|---|---|
| -> | integer | Get the nth element of the JSON array (the array element starts indexed from 0, and negative integers start to be calculated from the end) | ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json->2 | {“c”:“baz”} |
| -> | text | Obtain the JSON object domain by key | ‘{“a”: {“b”:“foo”}}’::json->‘a’ | {“b”:“foo”} |
| ->> | integer | Obtain the nth element of the JSON array in text form | ‘[1,2,3]’::json->>2 | 3 |
| ->> | text | Obtaining JSON object domains as text | ‘{“a”:1,“b”:2}’::json->>‘b’ | 2 |
| #> | text[] | Gets a JSON object in the specified path, where the path element can be a field key or an array index | ‘{“a”: {“b”:{“c”: “foo”}}}’::json#>‘{a,b}’ | {“c”: “foo”} |
| #>> | text[] | Get a JSON object in the specified path as text | ‘{“a”:[1,2,3],“b”:[4,5,6]}’::json#>>‘{a,2}’ | 3 |
Notice
For both json and jsonb types, these operators have their parallel variants. The domain/element/path extraction operator returns the same type as its left-hand input (json or jsonb), but those specified to return text will be forced to text. If the JSON input does not have the correct structure to match the request (for example, such elements do not exist), these domain/element/path extraction operators will return NULL instead of failing. The domain/element/path extract operator that accepts integer JSON array subscripts support negative subscripts at the end of the array.
Extra jsonb operator
| Operator | Right operand type | describe | example | Example results |
|---|---|---|---|---|
| @> | jsonb | Does the JSON value on the left contain the JSON path/value item on the right of the top level? | ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb | true |
| <@ | jsonb | Is the JSON path/value on the left included in the JSON value on the right of the top level? | ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb | true |
| ? | text | Does the string exist in the JSON value as a top-level key value? | ‘{“a”:1, “b”:2}’::jsonb ? ‘b’ | true |
| ?| | text[] | Does any of these array strings exist as top-level key values? | ‘{“a”:1, “b”:2, “c”:3}’::jsonb ?| array[‘b’, ‘c’] | true |
| ?& | text[] | Do these array strings exist as top-level key values? | ‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’] | true |
| || | jsonb | Connect two jsonb values to new jsonb values | ‘[“a”, “b”]’::jsonb || ‘[“c”, “d”]’::jsonb | [“a”, “b”, “c”, “d”] |
| - | text | Remove key/value pairs or string elements from the left operand. Match key/value pairs based on key value. | ‘{“a”: “b”}’::jsonb - ‘a’ | {} |
| - | text[] | Remove multiple key/value pairs or string elements from the left operand. Key/value pairs match based on their key values. | ‘{“a”: “b”, “c”: “d”}’::jsonb - ‘{a,c}’::text[] | {} |
| - | integer | Deletes the array element (negative integer ending) of the specified index. If the top-level container is not an array, an error is thrown. | ‘[“a”, “b”]’::jsonb - 1 | [“a”] |
| #- | text[] | Delete the domain or element of the specified path (JSON array, end of negative integer) | ‘[“a”, {“b”:1}]’::jsonb #- ‘{1,b}’ | [“a”, {}] |
Notice
Operators join elements at the top level of each operand. It does not operate recursively. For example, if both operands are objects with common key field names, the field value in the result will only be the value of the operand from the right.
JSON/JSONB Create a function
| function | describe | example | Example results |
|---|---|---|---|
| to_json(anyelement) to_jsonb(anyelement) |
Return the value to json or jsonb. Arrays and combinations are converted (recursively) into arrays and objects; otherwise, if there is a projection from the type to json, the projection function will be used to perform the conversion; otherwise a scalar value will be generated. For any scalar type of numerical, boolean or null value, its text expression will be used to make it a valid json or jsonb value in such a way. | to_json(‘Fred said “Hi.”’::text) | “Fred said “Hi.”” |
| array_to_json(anyarray [, pretty_bool]) | Return the array as a JSON array. A PostgreSQL multidimensional array will become a JSON array of arrays. If pretty_bool is true, a newline will be added between the elements in dimension 1. | array_to_json(‘{{1,5},{99,100}}’::int[]) | [[1,5],[99,100]] |
| row_to_json(record [, pretty_bool]) | Return the row as a JSON object. If pretty_bool is true, a newline will be added between the layer 1 elements. | row_to_json(row(1,‘foo’)) | {“f1”:1,“f2”:“foo”} |
| json_build_array(VARIADIC “any”) jsonb_build_array(VARIADIC “any”) |
Construct a JSON array that may contain heterogeneous types from a list of mutable parameters. | json_build_array(1,2,‘3’,4,5) | [1, 2, “3”, 4, 5] |
| json_build_object(VARIADIC “any”) jsonb_build_object(VARIADIC “any”) |
Construct a JSON object from a mutable parameter list. Through conversion, this parameter list consists of alternate keys and values. | json_build_object(‘foo’,1,‘bar’,2) | {“foo”: 1, “bar”: 2} |
| json_object(text[]) jsonb_object(text[]) |
Construct a JSON object from a text array. The array must be a one-dimensional array with an even number of members (members are treated as alternate key/value pairs), or a two-dimensional array (each inner array has exactly 2 elements, which can be regarded as a key/value pair). | json_object(‘{a, 1, b, “def”, c, 3.5}’) json_object(‘{{a, 1},{b, “def”},{c, 3.5}}’) |
{“a”: “1”, “b”: “def”, “c”: “3.5”} |
| json_object(keys text[], values text[]) jsonb_object(keys text[], values text[]) |
This form of json_object gets key/value pairs from two independent arrays. In other respects, the same form as a parameter. | json_object(‘{a, b}’, ‘{1,2}’) | {“a”: “1”, “b”: “2”} |
Notice
array_to_jsonandrow_to_jsonandto_jsonHave the same behavior, but they provide better printing options. The behavior described for to_json also applies to each value converted by other JSON creation functions.
JSON/JSONB processing functions
| function | Return value | describe | example | Example results |
|---|---|---|---|---|
| json_array_length(json) jsonb_array_length(jsonb) |
int | Returns the number of elements in the outermost JSON array. | json_array_length(‘[1,2,3,{“f1”:1,“f2”:[5,6]},4]’) | 5 |
| json_each(json) jsonb_each(jsonb) |
setof key text, value json setof key text, value jsonb |
Extend the outermost JSON object into a set of key/value pairs. | select * from json_each(‘{“a”:“foo”, “b”:“bar”}’) | key | value -----±------ a | “foo” b | “bar” |
| json_each_text(json) jsonb_each_text(jsonb) |
setof key text, value text | Extend the outermost JSON object into a set of key/value pairs. The return value will be the text type. | select * from json_each_text(‘{“a”:“foo”, “b”:“bar”}’) | key | value -----±------ a | foo b | bar |
| json_extract_path(from_json json, VARIADIC path_elems text[]) jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[]) |
json jsonb |
Returns the JSON value pointed to by path_elems (equivalent to the #> operator). | json_extract_path(‘{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’,‘f4’) | {“f5”:99,“f6”:“foo”} |
| json_extract_path_text(from_json json, VARIADIC path_elems text[]) jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[]) |
text | Returns the JSON value pointed to by path_elems (equivalent to the #>> operator) as text. | json_extract_path_text(‘{“f2”:{“f3”:1},“f4”:{“f5”:99,“f6”:“foo”}}’,‘f4’, ‘f6’) | foo |
| json_object_keys(json) jsonb_object_keys(jsonb) |
setof text | Returns the set of keys in the outermost JSON object. | json_object_keys(‘{“f1”:“abc”,“f2”:{“f3”:“a”, “f4”:“b”}}’) | json_object_keys ------------------ f1 f2 |
| json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb) |
anyelement | Extend the object from_json into a row whose columns match the record type defined by the base (see comments below). | select * from json_populate_record(null::myrowtype, ‘{“a”: 1, “b”: [“2”, “a b”], “c”: {“d”: 4, “e”: “a b c”}}’) | a | b | c —±----------±------------ 1 | {2,“a b”} | (4,“a b c”) |
| json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) |
setof anyelement | The outermost object array in extension from_json is a collection whose columns match the record type defined by base. | select * from json_populate_recordset(null::myrowtype, ‘[{“a”:1,“b”:2},{“a”:3,“b”:4}]’) | a | b —±– 1 | 2 3 | 4 |
| json_array_elements(json) jsonb_array_elements(jsonb) |
setof json setof jsonb |
Expand a JSON array into a collection of JSON values. | select * from json_array_elements(‘[1,true, [2,false]]’) | value ----------- 1 true [2,false] |
| json_array_elements_text(json) jsonb_array_elements_text(jsonb) |
setof text | Expand a JSON array into a collection of text values. | select * from json_array_elements_text(‘[“foo”, “bar”]’) | value ----------- foo bar |
| json_typeof(json) jsonb_typeof(jsonb) |
text | Returns the type of the outermost JSON value as a text string. Possible types are: object, array, string, number, boolean, and null. | json_typeof(‘-123.4’) | number |
| json_to_record(json) jsonb_to_record(jsonb) |
record | Build an arbitrary record from a JSON object (see notes below). Just like all functions that return record, the caller must explicitly define the structure of the record with an AS clause. | select * from json_to_record(‘{“a”:1,“b”:[1,2,3],“c”:[1,2,3],“e”:“bar”,“r”: {“a”: 123, “b”: “a b c”}}’) as x(a int, b text, c int[], d text, r myrowtype) | a | b | c | d | r —±--------±--------±–±-------------- 1 | [1,2,3] | {1,2,3} | | (123,“a b c”) |
| json_to_recordset(json) jsonb_to_recordset(jsonb) |
setof record | Construct an arbitrary collection of records from an array of JSON objects (see notes below). Just like all functions that return record, the caller must explicitly define the structure of the record with an AS clause. | select * from json_to_recordset(‘[{“a”:1,“b”:“foo”},{“a”:“2”,“c”:“bar”}]’) as x(a int, b text); | a | b —±---- 1 | foo 2 | |
| json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb) |
json jsonb |
Returns from_json with a null object field. Other null values remain unchanged. | json_strip_nulls(‘[{“f1”:1,“f2”:null},2,null,3]’) | [{“f1”:1},2,null,3] |
| jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) | jsonb | If create_missing is true (default is true) and the specified part by path does not exist, then the target is returned, which has the specified part of the path, the replacement part of the new_value, or the addition part of the new_value. As with the path-oriented operator, negative integers appear in the path>count at the end of the JSON array. | jsonb_set(‘[{“f1”:1,“f2”:null},2,null,3]’, ‘{0,f1}’,‘[2,3,4]’, false) jsonb_set(‘[{“f1”:1,“f2”:null},2]’, ‘{0,f3}’,‘[2,3,4]’) |
[{“f1”:[2,3,4],“f2”:null},2,null,3] [{“f1”: 1, “f2”: null, “f3”: [2, 3, 4]}, 2] |
| jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean]) | jsonb | Returns the target with new_value inserted. If the target section specified by path is in a JSONB array, new_value will be inserted before (insert_after is false, default) or after (insert_after is true). If the target section specified by path is within a JSONB object, new_value is inserted only if the target does not exist. For path-oriented operators, negative integers appearing in path represent counting starting from the end of the JSON array. | jsonb_insert(‘{“a”: [0,1,2]}’, ‘{a, 1}’, ‘“new_value”’) jsonb_insert(‘{“a”: [0,1,2]}’, ‘{a, 1}’, ‘“new_value”’, true) |
{“a”: [0, “new_value”, 1, 2]} {“a”: [0, 1, “new_value”, 2]} |
| jsonb_pretty(from_json jsonb) | text | Return from_json as indented JSON text. | jsonb_pretty(‘[{“f1”:1,“f2”:null},2,null,3]’) | [ { “f1”: 1, “f2”: null }, 2, null, 3 ] |
| jsonb_concat(jsonb, jsonb) | jsonb | Concatenate two jsonb values to the new jsonb value. | select jsonb_concat(‘{“a”: “b”,“test”:“v”}’::jsonb,‘{“test”:[{“name”: “r”}]}’::jsonb); | {“a”: “b”, “test”: [{“name”: “r”}]} |
| jsonb_contains(jsonb, jsonb) | boolean | Determine whether the first jsonb contains the second jsonb (equivalent to the || operator). | select jsonb_contains(‘{“a”: “b”,“test”: [{“name”: “v”}]}’::jsonb,‘{“test”:[{“name”: “v”}]}’::jsonb); | true |
Notice
existjson_populate_record、json_populate_recordset、 json_to_recordandjson_to_recordsetIn , the type from JSON is forced to "do your best" and may not get the desired value for some types. The JSON key will be matched with the same column name in the target row type. A JSON field that does not appear in the target row type will be ignored from the output, and a target column that does not match any JSON field will be simply NULL.
alljsonb_setThe path parameter item must exist in the target unlesscreate_missingIt's true, in this case, except for the last item. If these conditions do not meet the target returns unchanged.
If the last path item is an object key, it is created if it does not exist and gives a new value. If the last path item is an array index, if the setting item is positive by counting from the left, if the negative number is counted from the right -1 to specify the element to the right, etc. If the item exceeds the range -array_length … array_length -1 and create_missing is true, if the item is negative, a new value is added at the beginning of the array, and if it is a positive, a new value is added to the end of the array.
Don't put itjson_typeofThe null return value of the function is confused with the NULL of SQL. Although calling json_typeof('null'::json) will return null, calling json_typeof(NULL::json) will return a NULL of SQL.
If the argument to json_strip_nulls contains the copy field names of any object, the result may be semantically different, depending on the order in which they are generated. This is not a problem with jsonb_strip_nulls, because the jsonb value never copies the object field name.
Summarize
This is all about this article about PostgreSQL operation json/jsonb. For more related PostgreSQL operation json/jsonb, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!