postgresql之 json 、jsonb操作

                  --string 转 json  
                 select '{"Items":[{"Id":1,"InitStartDate":"2018-07-01T00:00:00","InitEndDate":"2018-11-13T11:46:59.461722+08:00",
					 "CurrentStartDate":"","ProcessPercent":0,"State":0}]}'::jsonb into task_config ; 
                   --timestamp 转 字符串，字符串 转 json
		    select  concat('"', to_char(start_date,'YYYY-MM-DD'), '"')::jsonb into startj_date;
		    select  concat('"', to_char(end_date,'YYYY-MM-DD'), '"')::jsonb into endj_date;
                   --修改json中的值
			select jsonb_set(task_config, '{Items,0,InitStartDate}', startj_date, false) into task_config;
			select jsonb_set(task_config, '{Items,0,InitEndDate}',endj_date, false) into task_config;
			RAISE NOTICE 'update task. 
			original TaskConfig:%.
		    new TaskConfig:%',r_bra."TaskConfig",task_config;	            

　

json 查询 ：

json转为jsonb 进行查询　

 

select "Numbers" ,("Numbers")::jsonb, ,("Numbers")::text
--text:like
--jsonb:@>
from request."Request"  
where 
"Numbers"::jsonb @> '2108126984966095';