I won't say much nonsense, let's just read the code~
UPDATE tablename SET tags = jsonb_set(tags-'landuse_area', '{landuse_area}',('"' || round((ST_Area(ST_Transform(geom,4527)) * 0.0015) :: NUMERIC,3) || '"')::jsonb, TRUE) WHERE tags @> '{"name":"Zhang San"}';
The purpose of round is to retain 3 decimal places
st_area is to calculate the area of the polygon, and the following 0.0015 is the area unit converted into mu.
st_transform is projection conversion. Here the original geom in my table is 4326, which is not suitable for calculating area, so here it is converted to 4527 to calculating area
::NUMERIC is converted to numeric data type
::jsonb is converted to jsonb type
Supplement: Postgresql----address, delete, modify and check the JSON and JSONB types
Postgresql supports two types of json data: json and jsonb. The only difference between the two is efficiency. Json is a complete copy of the input and parses it when used, so it retains the input spaces, duplicate keys and order, etc. jsonb is a binary saved after parsing the input. It deletes unnecessary spaces and duplicate keys when parsing, and the order and input may be different. There is no need to parse again when using it. Both of them keep the last key-value pair when processing the duplicate key. The difference in efficiency: json type storage is fast and slow to use, jsonb type storage is slightly slower and faster to use.
Note: Key value pairs must use double quotes
Since PostgreSQL 9.3, json has become a data type in postgres. That is, like varchar and int, the type of a field in our table can be json.
At the same time, postgres also provides jsonb format, which is a binary form of json. The difference between the two is that json writes quickly, reads slowly, jsonb writes slowly, and reads quickly, but in terms of operation, there is no difference between the two. Let’s take jsonb as an example.
Create a table
Suppose the json data we want to store is like this:
{
"id": ID
"name":"name",
"age":age
}
The table creation statement is as follows:
create table if not exists name_age ( info jsonb )
OK, so I created a table with only one info field inside, and the CRUD operation is started below.
Insert data
Insert data can be inserted directly in json format:
insert into name_age values('{"id":1,"name":"Xiao Ming", "age":18}')
Insert a new key value gender in json, as follows:
SELECT info||'{"gender":"male"}'::jsonb from name_age where (info->>'id')::int4 = 1
Query data
Query in Postgres requires query characters. For example, we want to query data with id 1, and the statement is as follows:
select info from name_age where info @> '{"id":1}'::jsonb
The @> query is used to indicate whether there is a key-value pair with id 1 in the top json in the current record in info; if so, the conditions are met.
Let’s take another more complex query, query the record of age>16, and only display name, and the statement is as follows:
select info->'name' from name_age where (info->>'age')::int4 > 16
For detailed operator use, please refer to the official documentation:9.15. JSON Functions and Operators
Modify data
Below, change age from 18 to 22, SQL statement:
SELECT info ||'{"age":22}'::jsonb from name_age where (info->>'id')::int4 = 1
The above usage is only applicable to above 9.5 and below 9.5, the entire record needs to be updated, and a certain value cannot be modified separately.
In addition to operators, you can also use function operations: jsonb_set() , and the function signature is as follows:
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
For detailed use, please refer to9.15. JSON Functions and Operators
Delete data
Delete the key age, SQL is as follows:
SELECT info-'age' from name_age where (info->>'id')::int4 = 1
Just use the operator - directly.
Summarize
There are many convenient operators in PostgreSQL 9.5 or above, making it very convenient to operate json.
Operators for json and jsonb
| Operator | Right operand type | describe | Example | result |
| -> | int | Get JSON array elements (index starts at 0) | select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2; | {"c":"baz"} |
| -> | text | Get the value by key | select '{"a": {"b":"foo"}}'::json->'a'; | {"b":"foo"} |
| ->> | int |
Get JSON array element as text |
select '[1,2,3]'::json->>2; | 3 |
| ->> | text | Get the value as text by key | select '{"a":1,"b":2}'::json->>'b'; | 2 |
| #> | text[] |
Get the JSON object in the specified path |
select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'; | {"c": "foo"} |
| #>> | text[] |
Get the JSON object as text in the specified path |
select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'; | 3 |
jsonb extra operator
| Operator | Right operand type | describe | Example | result |
| @> | jsonb | Does the uppermost value of the left json include the right json object |
select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb; select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; |
f t |
| <@ | jsonb | Is the json object on the left included in the uppermost value of the json on the right | select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb; | t |
| ? | text | Is text the key as the top layer of the left Json object? | select '{"a":1, "b":2}'::jsonb ? 'b'; | t |
| ?| | text[] | Whether any element in text[] is used as the top key of the left Json object | select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']; | t |
| ?& | text[] | Whether all elements in text[] are used as keys on the uppermost level of the Json object on the left | select '["a", "b"]'::jsonb ?& array['a', 'b']; | t |
| || | jsonb | Connect two json objects to form a new json object | select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb; | ["a", "b", "c", "d"] |
| - | text | Delete the key-value pair with the key text in the json object on the left | select '{"a": "b"}'::jsonb - 'a'; | {} |
| - | integer |
Deletes the element at the specified index of the array, and if the index value is negative, the index value is calculated from the right. If the uppermost container is not an array, an error is thrown. |
select '["a", "b"]'::jsonb - 1; | ["a"] |
| #- | text[] |
Delete the domain or element under the specified path (if it is a json array and the integer value is negative, Then the index value starts from the right) |
select '["a", {"b":1}]'::jsonb #- '{1,b}'; | ["a", {}] |
json create function
| function | describe | Example | result |
|
to_json(anyelement) to_jsonb(anyelement) |
Returns a value of type json or jsonb. Arrays and composites are converted (recursively) into arrays and objects. In addition, the numbers, In addition to boolean and NULL values (using NULL directly to throw an error), other scalars must have type conversion. (Please refer to the original text here) |
select to_json('3'::int); | 3 |
|
array_to_json(anyarray [, pretty_bool]) |
Returns the array as a JSON array. PostgreSQL multi-dimensional array becomes an array in a JSON array. |
select array_to_json('{{1,5},{99,100}}'::int[],true); |
[[1,5], + |
| row_to_json(record [, pretty_bool]) | Returns the row as a JSON object. If pretty_bool is true, add a line break between level 1 elements. | select row_to_json(row(1,'foo'),true); |
{"f1":1, + |
|
json_build_array(VARIADIC "any") jsonb_build_array(VARIADIC "any") |
Create a different type of JSON array consisting of a list of variable parameters | select json_build_array(1,2,'3',4,5); | [1, 2, "3", 4, 5] |
|
json_build_object(VARIADIC "any") jsonb_build_object(VARIADIC "any") |
Create a JSON object composed of a list of mutable parameters. Parameter list parameters are converted alternately into keys and values. | select json_build_object('foo',1,'bar',2); | {"foo" : 1, "bar" : 2} |
|
json_object(text[]) jsonb_object(text[]) |
Create a json object based on the text[] array. If it is a one-dimensional array, there must be an even number of Elements, elements alternate to form keys and values. If it is a two-dimensional array, each element must have 2 elements, which can form a key-value pair. |
select json_object('{a, 1, b, "def", c, 3.5}'); select 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[]) |
Get keys and values from two sets of text[], which are similar to one-dimensional arrays. | select json_object('{a, b}', '{1,2}'); | {"a" : "1", "b" : "2"} |
json processing function
| function | Return type | describe | Example | result |
|
json_array_length(json) jsonb_array_length(jsonb) |
int | Returns the number of outermost elements of the Json array | select 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 |
Convert the outermost Json object to a collection of key-value pairs | select json_each('{"a":"foo", "b":"bar"}'); |
(a,"""foo""") |
|
json_each_text(json) jsonb_each_text(jsonb) |
setof key text, value text | Convert the outermost Json object to a key-value pair set, and the value is of type text | select json_each_text('{"a":"foo", "b":"bar"}'); |
(a,foo) |
|
json_extract_path(from_json json, VARIADIC path_elems text[]) jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[]) |
json jsonb |
Returns the value pointed to by path_elems, same as operator #> | select 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 value pointed to by path_elems and converts it to text type, same as operator #>> | select 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 | Return the outermost key of the json object | select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}'); |
f1 |
|
json_populate_record(base anyelement, from_json json) jsonb_populate_record(base anyelement, from_json jsonb) |
anyelement | Return the value of the json object as the row type defined by the base. If the row type field is less than the json object's key value, the extra key value will be discarded; if there are more row type fields, the extra fields will be automatically filled with NULL. |
Table tbl_test definition: Table "public.tbl_test" c | character varying(32) | select * from json_populate_record(null::tbl_test, '{"a":1,"b":2}'); |
a | b | c |
|
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) |
setof anyelement | Return the outermost array of json object as the row type defined by base |
Table definition is the same as above select * from json_populate_recordset(null::tbl_test, '[{"a":1,"b":2},{"a":3,"b":4}]'); |
a | b | c |
|
json_array_elements(json) jsonb_array_elements(jsonb) |
setof json setof jsonb |
Convert json array into a collection of json object value | select json_array_elements('[1,true, [2,false]]'); |
1 |
|
json_array_elements_text(json) jsonb_array_elements_text(jsonb) |
setof text | Convert json array to value collection of text | select json_array_elements_text('["foo", "bar"]'); |
foo |
|
json_typeof(json) jsonb_typeof(jsonb) |
text |
Returns the data type of the outermost value of json, the possible types are object, array, string, number, boolean, and null. |
select json_typeof('-123.4') | number |
|
json_to_record(json) jsonb_to_record(jsonb) |
record | Create a record type record based on the json object. All functions return record type, so the structure of record must be clearly defined using as. | select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text); |
a | b | d |
|
json_to_recordset(json) jsonb_to_recordset(jsonb) |
setof record | Create a record type record based on the json array. All functions return record type, so the structure of record must be explicitly defined using as. | select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); |
a | b |
|
json_strip_nulls(from_json json) jsonb_strip_nulls(from_json jsonb) |
json jsonb |
Returns all non-null data in the json object, and other nulls are reserved. |
select 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, a new jsonb will be appended at the path of the target; if false, the value at the path will be replaced. |
select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false); select 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 | If insert_after is true, insert a new value after the path of the target, otherwise insert before the path. |
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); select 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 | Returns json objects in an indented format easier to read | select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]'); |
[ |
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.