PostgreSQL中的json解析

• 1、PostgreSQL json 数组解析

-- INSERT INTO "dwd"."dwd_test"(id, cname, is_deleted)
-- 数据格式为：[{}, {}, ...]
-- 字典的 key='value'
-- cnames 为需要解析的字段
SELECT 
	id,
	(CASE 
		WHEN cnames LIKE '%}]' 
		THEN json_array_elements(REPLACE(cnames, '\u0000', '')::json)->>'value' 
	END) AS cname,
	is_deleted
from 
	"ods"."ods_test"
WHERE
	is_deleted='f'
  	AND cnames LIKE '%}]'

注意：::json也可以用json(xxx)代替

• 2、MySQL json 数组解析

-- [{}, {}, ...]
-- 用json_unquote函数去掉双引号，而不是replace，哪种更优雅一目了然。但是我认为 ->> 最优雅。
SELECT json_unquote(json_extract( a, '$[0]."text"' ))  AS b FROM `table_name` WHERE id=424;
SELECT json_unquote(json_extract( a, '$[*]."text"' ))  AS b FROM `table_name` WHERE id=424;
SELECT a ->> '$[0].text' AS b FROM `table_name` WHERE id=424;
SELECT a ->> '$[*].text' AS b FROM `table_name` WHERE id=424;
SELECT REPLACE(a ->> '$[*].text','\"，\",','')  AS b FROM `table_name` WHERE id=424;

• PostgreSQL 取json的第一个元素

假设要解析的json字段名为json_field
若json_field格式为：[[10], [20, 30]]
SELECT json_field -> 0;

若json_field格式为：[{'key': value}]
SELECT json_field -> 0 ->>'key' FROM table_name;

PostgreSQL JSON字段的 ->> #>> 取值操作

SELECT
data#>>‘{_id, $oid}’ as trace_id,
cast(data#>>‘{cid, $numberLong}’ as int8) as cid,
cast(data#>>‘{sid, $numberLong}’ as int8) as sid,
cast(data#>>‘{percent, $numberDecimal}’ as numeric(40, 10)) as percent,
cast(data->>‘is_deleted’ as bool) as is_deleted
FROM “ods”.“ext_ods_mongo_t_xxxt”;

– 测试发现第一层是json，第二层是text。
select data, pg_typeof(data) FROM “ods”.“ext_ods_mongo_t_xxx” limit 1;
select data, pg_typeof(data->>‘_id’) as _id FROM “ods”.“ext_ods_mongo_t_xxx” limit 1;
#>>表示获取指定路径的一个JSON对象的字符串
SELECT data#>>‘{_id, $oid}’ as trace_id

https://blog.csdn.net/wangzhi291/article/details/102485976