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)代替
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;
假设要解析的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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)