What is jsonb
The data types json and jsonb defined by PostgreSQL documents are almost the same; the key difference is that json data is stored as an exact copy of JSON input text, while jsonb stores data in decomposed binary form; that is, it is not an ASCII/UTF-8 string, but a binary code.
This article mainly talks about how to query and modify the jsonb format data in postgresql as you wish
1. Query
Simple query
# Stores data in key-value format, and obtains the corresponding value through the specified key# Use -> to return the result in quotesselect '{"nickname": "goodspeed", "avatar": "avatar_url"}'::json->'nickname' as nickname;
# Use ->>Return results without quotesselect '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' as nickname;
Complex query
# {tags,0} represents the query key='tags', and the value is the first element of the array in the arrayselect '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' as tag;
# {aa,b,0} represents the query key='aa', in the corresponding value, key='b', the first element of the arrayselect '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"], "aa":{"b": [{"c": 1}]}}'::jsonb#>>'{aa,b,0}';
2. Modification
Revise
jsonb_set(Original data, Locations that need to be changed, Replaced value，Added if it does not exist),
select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
This is the article about postgresql's jsonb data query and modification. For more related postgresql jsonb data query and modification, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!