PGsql 解析json及json数组

创建测试数据

drop table if exists json_test;
create table json_test as
select
'111' as id,
'{"nodes":{"1692328028076":{"nodeId":"1692328028076","nodeName":"测试表1","nodeType":"DATACOLLECT","nodeConfig":{"nodeName":"测试表1","retryTimes":3,"configNoId":"1691325111866490880","configNo":"1691325111866490880","iocNo":"","id":"1692328028076"}},"1692328056195":{"nodeId":"1692328056195","nodeName":"测试表2","nodeType":"DATASYNC","nodeConfig":{"nodeName":"测试表2","retryTimes":3,"configNoId":"1691324890486931456","configNo":"1691324890486931456","iocNo":"","id":"1692328056195"}},"1692340398058":{"nodeId":"1692340398058","nodeName":"测试表3","nodeType":"HIVESQL","nodeConfig":{"nodeName":"测试表3","retryTimes":3,"configNoId":"1691324637188718592","configNo":"1691324637188718592","iocNo":"","id":"1692340398058"}}},"linkList":[{"name":"polyline","id":"258b0971","toId":"1699601569911","fromId":"1692340398058"},{"name":"polyline","id":"ac3c907","toId":"4db01ba","fromId":"1692340417936"},{"name":"polyline","id":"3db3281f","toId":"f57d111","fromId":"1692328056195"}]}'
 as json_data
union all
select
'222' as id,
'{"nodes":{"1692328028076":{"nodeId":"1692328028076","nodeName":"测试表4","nodeType":"DATACOLLECT","nodeConfig":{"nodeName":"测试表4","retryTimes":3,"configNoId":"1691325111866490880","configNo":"1691325111866490880","iocNo":"","id":"1692328028076"}},"1692328056195":{"nodeId":"1692328056195","nodeName":"测试表5","nodeType":"DATASYNC","nodeConfig":{"nodeName":"测试表5","retryTimes":3,"configNoId":"1691324890486931456","configNo":"1691324890486931456","iocNo":"","id":"1692328056195"}},"1692340398058":{"nodeId":"1692340398058","nodeName":"测试表6","nodeType":"HIVESQL","nodeConfig":{"nodeName":"测试表6","retryTimes":3,"configNoId":"1691324637188718592","configNo":"1691324637188718592","iocNo":"","id":"1692340398058"}}},"linkList":[{"name":"polyline","id":"258b0971","toId":"1699601569911","fromId":"1692340398058"},{"name":"polyline","id":"22ac3c907","toId":"224db01ba","fromId":"22692340417936"},{"name":"polyline","id":"223db3281f","toId":"22f57d111","fromId":"221692328056195"}]}'
 as json_data

数据展示

select
id,
json_data :: json -> 'nodes' as nodes,
json_each(json_data :: json -> 'nodes') as each_nodes
from
json_test

问题一

查询出nodes中nodeConfig对应的key和value

select 
id,
nodes::json->'nodeId' as nodeId,
nodes::json->'nodeType' as nodeType,
nodes::json->'nodeName' as nodeName,
replace(json_each(nodes::json->'nodeConfig')::varchar,'""','') as nodeConfig,
replace(regexp_replace(replace(json_each(nodes::json->'nodeConfig')::varchar,'""','')::varchar,',.*$','',''),'(','') as nodeconfig_key,
replace(replace(regexp_replace(replace(json_each(nodes::json->'nodeConfig')::varchar,'""','')::varchar,'^([^,]*,)','',''),'"""',''),')','') as nodeconfig_value

from 
(
select
id,
replace(substring(regexp_replace(each_nodes :: varchar, '^([^,]*,)', '')
from 2 for length(regexp_replace(each_nodes :: varchar, '^([^,]*,)', '')) -3),'""','"') as nodes
from
(
select
id,
json_each(json_data :: json -> 'nodes') as each_nodes
from
json_test
) t
) t

问题二

查询出linkList中的fromId和toId

select
id,
json_array_elements(json_data :: json -> 'linkList') as linkList,
json_array_elements(json_data :: json -> 'linkList')->'fromId' as fromId,
json_array_elements(json_data :: json -> 'linkList')->'toId' as toId
from
json_test