postgreSQL解析json字符串字段

1. 转化和解析

SELECT cast(_app as jsonb)->>'updated' app_updatetime,  _id AS id, _id AS title, _full_text AS CONTENT, _app AS app, _files AS files,info AS info FROM
process_issue WHERE _files != '[ ]'
and cast(_app as jsonb)->>'updated' > '2020-09-06 07:01:42'

2.使用相关函数

        with max_pss_version as
        (
        select product_id,max(replace(replace(version,'Ver.',''),'_','')::BIGINT) as version from smart_pss_basic_info
        where status = 'fa6487c221ca467e879f3c274d01c953'
				and product in ('BERLIN NA')
        group by product_id
        )
        ,max_pss as(
        select spbi.* from smart_pss_basic_info as spbi inner join max_pss_version as mpv on
        spbi.product_id=mpv.product_id and (replace(replace(spbi.version,'Ver.',''),'_','')::BIGINT)=mpv.version
        )
				, pss_detail as(
        select mp.product,spd.* from max_pss as mp inner join smart_pss_detail as spd on mp.id=spd.pss_id
        where
        (spd.process='Ship FG' AND spd.factory!='AR') OR (spd.process='FE PCBA' AND spd.factory='AR')
        ORDER BY mp.product
				)
				SELECT (cast(pss_plan_info as jsonb)->>0)::jsonb->>'date'  from pss_detail
				SELECT cast(pss_plan_info as jsonb)->>'date'  from pss_detail
				SELECT product,pss_plan_info::json->'date' from pss_detail
				SELECT product,json_array_element(pss_plan_info::json,0)->'date' from pss_detail