Preface
Since Postgresql is used in the project and stores some unstructured json data, which contains statistics and records, and has nested relationships, you need to understand how to query and process JSON data in Postgresql.
Postgresql：9.6
Official document: /docs/9.6/#FUNCTIONS-JSON-CREATION-TABLE
A basic order table structure:
-- ordersurface "id" bigserial primary key, "order_id" varchar(55) COLLATE "default", "product_id" int8, "order_json" text COLLATE "default", "create_time" timestamp(6),
Background knowledge: json and jsonb operators
| Operator | Right operand type | describe | example | Example results |
|---|---|---|---|---|
| -> | int | Obtain JSON array elements (index starts at 0 and ends with negative integers) | ‘[{“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”} |
| ->> | int | Obtaining JSON array elements in text | ‘[1,2,3]’::json->>2 | 3 |
| ->> | text | Obtaining JSON object domains as text | ‘{“a”:1,“b”:2}’::json->>‘b’ | 2 |
| #> | text[] | Gets the JSON object in the specified path | ‘{“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 |
Q: How to view the key content specified by JSON?
Syntax via ::json
select order_json::json->'orderBody' from order -- Object Domain
select order_json::json->>'orderBody' from order -- text
select order_json::json#>'{orderBody}' from order -- object domainselect order_json::json#>>'{orderBody}' from order -- TextThere are more jsonb operators and json operation functions in the official documentation
Q: How to deal with multi-layer nested JSON?
It is to use the basic JSON syntax, pay attention to whether the result is an object domain or text. The object domain can continue to use fields, and the text cannot continue to view JSON.
select '{"sites":{"site":{"id":"1","name":"","url":""}}}'::json->'sites'->'site' -- Object Domain
select '{"sites":{"site":{"id":"1","name":"","url":""}}}'::json->'sites'->>'site' -- text
Q: How to deal with JSON arrays?
It is also through the basic operation of JSON, first locate the key where the array object is located. After obtaining the corresponding value through the key, directly ->(0), you can use the corresponding object domain. Pay attention to the object domain and text. If you convert it into text, you cannot get the key and specific data.
There are many other methods related to JSON, please refer to the official documentation for details
select '{"sites":{"site":[{"id":"1","name":"","url":""},{"id":"2","name":"Rookie Tool","url":""},{"id":"3","name":"Google","url":""}]}}'::json->'sites'->'site'->(0)
select '{"sites":{"site":[{"id":"1","name":"","url":""},{"id":"2","name":"Rookie Tool","url":""},{"id":"3","name":"Google","url":""}]}}'::json->'sites'->'site'->(0)->>'id'
Extension: How to use the last object data of a JSON array?
select json_array_length('{"sites":{"site":[{"id":"1","name":"","url":""},{"id":"2","name":"Rookie Tool","url":""},{"id":"3","name":"Google","url":""}]}}'::json->'sites'->'site') -- Query the length of json data
select '{"sites":{"site":[{"id":"1","name":"","url":""},{"id":"2","name":"Rookie Tool","url":""},{"id":"3","name":"Google","url":""}]}}'::json->'sites'->'site'->(json_array_length('{"sites":{"site":[{"id":"1","name":"","url":""},{"id":"2","name":"Rookie Tool","url":""},{"id":"3","name":"Google","url":""}]}}'::json->'sites'->'site') -1)
Q: How to replace the content in a JSON string?
Check out the official syntax through the select statement
| function | Return value | describe | example | Example results |
|---|---|---|---|---|
| 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. | (1)jsonb_set(‘[{“f1”:1,“f2”:null},2,null,3]’, ‘{0,f1}’,‘[2,3,4]’, false) (2)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] |
The official description is quite clear: jsonb_set method
- The first parameter needs to be the object domain of jsonb
- The second parameter is to access the path of the corresponding value (note that the syntax of this path can be {a,b}, the key-b in the table name key-a, and see (2) in the table for data)
- The third parameter is a new value to replace the value of the second parameter key in the first parameter.
- The fourth parameter, if create_missing is true (default is true) and the specified part by path does not exist, then returns a target, which has the specified part of the path, the replacement part of the new_value, or the addition part of the new_value. As path-oriented operators, negative integers appear in path>count at the end of JSON array
Refer to the official documentation, a simple content replacement
select jsonb_set(order_json::jsonb,'{premsg}','test'::jsonb) from order
So if it is a nested multi-layer JSON value, can it be replaced? -Yes, the syntax is the same, you need to locate the specified field
select jsonb_set((order_json::json->>'rspDesc')::jsonb, '{preOrder}', '"11111"'::jsonb) from order
The above is the select statement, so how do you write the specific update statement?
grammar：UPDATE show set List name = (jsonb_set(List name::jsonb,'{key}','"value"'::jsonb)) where condition
update order set order_json = jsonb_set(order_json::jsonb,'{rspDesc}',(jsonb_set((event_json::json->>'rspDesc')::jsonb, '{preNumber}', '"999999999"'::jsonb)::jsonb)) -- You need to replace the content that needs to be modified first，Then update and replace，At this timerspDescIt is the object domain format
There are basically no successful examples of execution on the Internet, so record them here.
Summarize
This is the end of this article about PostgreSQL querying and processing JSON data. For more related content related to pgSQL processing JSON data, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!