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
wherestatus='fa6487c221ca467e879f3c274d01c953'and product in('BERLIN NA')groupby product_id
),max_pss as(select spbi.*from smart_pss_basic_info as spbi innerjoin 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 innerjoin 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')ORDERBY 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
process_issue 表中字段’_app’为json字符串类型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 FROMprocess_issue WHERE _files != '[ ]'and cast(_app as jsonb)->