postgresのjsonbプロパティの利用について

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

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false)
Result: [{"f1":[2,3,4],"f2":null},2,null,3]
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]')
Result: [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

-- attributes are jsonb type fields (objects converted to json)
original value: {"a":"1"}
update user_test set attributes = jsonb_set(attributes,'{a}','"0"'::jsonb, false) where id = '8888';
After execution: {"a":"0"}

-- After execution the attributes field adds platform: baidu
update user_test set attributes = attributes::jsonb || '{"platform":"baidu"}'::jsonb;
Alternatively.
update user_test set attributes = jsonb_set(attributes, '{platform}','"baidu"');

select value from json_each('{"a":"foo", "b":"bar"}') where key = 'a'
select * from json_object_keys('{"a":"foo", "b":"bar"}')
select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b& quot;}}')
select * from json_object_keys(from ci_type.attributes);--error
select * from to_jsonb('"a":1,"b":2') 
 select '{"a":1,"b":2}'::json->>'b' --get the value of the corresponding key in jsonb (text)
 --select * from json_each( to_jsonb(select distinct attributes from ci_type ) )
 --select to_jsonb(select distinct attributes from ci_type ) 
 
--extend the field to extract the value of the corresponding attribute
  select attributes :: json->>'instanceType' from ci_type 
-- attributes values to jsonb
select to_jsonb('id:'||id::text) from ci
--jsonb add attribute, delete attribute
select '{"a":"foo", "b":"bar"}'::jsonb || '{"c":"fc", "d":" bdd"}'::jsonb - add
select '{"a":"foo", "b":"bar"}'::jsonb -'c'-'d'-'a'|||'{"a":2}' - delete
select '{"a": "b","c":3}'::jsonb - 'a'
-- Get json object by path: #>
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{ b,ba}'
Result: "b1"
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{ b}'
Result: {"ba":"b1","bb":"b2"}
-- Get json object as text based on path: #>>
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #>& gt; '{b,ba}'
Result: "b1"

jsonb stores the millisecond value field
# Update the create_time field in the attributes field in the user table to the current time
update user_test
set attributes = jsonb_set(attributes,'{create_time}',to_jsonb(extract(epoch from now())*1000), true)

EXTRACT(field FROM source)

EXAMPLE:select extract(year from now());

create table person 
(id int, -- unique identifier
label jsonb); -- array of labels for the person (specifying which company the person is from), one object at a time

[{"id":1,"code":"p123","name":"ali"},{"id":2,"code":" p123","name":"ali"}]

-- when label is null
update person set label = '{"id":1,"code":"p123","name":"ali"}'::jsonb;

-- run when label is not null
update person set label = '{"id":1,"code":"p123","name":"ali"}'::jsonb || label

update person set label = null;

-> ->> jsonb_array_elements() jsonb_build_array() array()

update person 
set label = jsonb_build_array(
    array( -- Without this function, an error will be reported when filtering out more than 2 hops of data, because the jsonb_build_array function can only have one json
        (select * from 
         (select jsonb_array_elements(label)j from person where id = 1) as a 
         where (j->>'id')::int <> 1) -- filter out the objects to be deleted
    )
)->0 -- if you don't add this you get two arrays of [[]]s
where id = 1;