PG/Guass Json处理样例
Json相关函数 :http://www.postgres.cn/docs/9.4/functions-json.html 或 PostgreSQL JSON 函数参考 https://www.sjkjc.com/postgresql-ref/json-functions/
关键点:
操作符 | 右操作数的类型 | 描述 | 示例 | 示例结果 |
---|---|---|---|---|
-> | int | 获取JSON数组元素(索引从0开始) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
-> | text | 通过秘钥获取JSON对象字段 | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
->> | int | 获取JSON数组元素为text | '[1,2,3]'::json->>2 | 3 |
->> | text | 获取JSON对象字段为text | '{"a":1,"b":2}'::json->>'b' | 2 |
#> | text[] | 在指定的路径获取JSON对象 | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
#>> | text[] | 在指定的路径获取JSON对象为text | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3 |
json_array_elements 函数
样例SQL:
数据解释:节点1下有2个子节点,11、12。每个节点有一个因子和因子值列表。
查询所有的因子以及因子值对的列表。
{
"id": "1",
"list": [{
"factorId": "1",
"factorValue": "a1,b1"
},
{
"factorId": "2",
"factorValue": "a2,b2"
}],
"chlid": [{
"id": "11",
"list": [{
"factorId": "11",
"factorValue": "a11,b11"
},
{
"factorId": "12",
"factorValue": "a12,b12"
}]
},
{
"id": "21",
"list": [{
"factorId": "21",
"factorValue": "a21,b21"
},
{
"factorId": "22",
"factorValue": "a22,b22"
}]
}]
}
关键点:
1、注意 ->与->> 差异,一个是获取json的值对象(可能还是json),一个值文本(已经是text类型)
2、json_array_elements json数组拆分为多行
3、string_to_array 字符串切分数组,unnest将数组转为多行
-- 数据结构,是多个属性,多个list嵌套
with tmp(json_data) as (select '{"id":"1","list":[{"factorId":"1","factorValue":"a1,b1"},{"factorId":"2","factorValue":"a2,b2"}],"chlid":[{"id":"11","list":[{"factorId":"11","factorValue":"a11,b11"},{"factorId":"12","factorValue":"a12,b12"}]},{"id":"21","list":[{"factorId":"21","factorValue":"a21,b21"},{"factorId":"22","factorValue":"a22,b22"}]}] }')
select factor ->> 'factorId' factorId , unnest(string_to_array(factor ->> 'factorValue', ',')) factorValue
from
(
select json_array_elements(json_data::json -> 'list') as factor from tmp
union all -- json 无法去重,只能用union all
select json_array_elements(json_array_elements(json_data::json -> 'chlid')->'list') from tmp
)
--针对json嵌套,且嵌套的已String形式作为Value,需要转义在解析
SELECT json - >> 'asetType' AS asettype,
json - >> 'objName' AS objname,
json - > 'assetJson' - >> 'extendObjName' extendobjname,
json_array_elements(json - > 'assetJson' - > 'fieldList') attr
FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(t."content", '\"', '"'), '"{', '{'),'}"','}'),'"[','['),']"',']'),'\\','\') ::json AS json
FROM table_xxx t
WHERE env_name = 'xxx'
AND tenant_id IN ('xxx')
AND asset_type = 'xxx'
AND asset_id = 'xxx')
针对json转义为字符串的解析
--利用正则捕获组将转移或者数组、对象,转化为对应的结构(利用转化为特殊字符串,在二次替代)
select replace(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(regexp_replace(regexp_replace(content,'((\\\")|(\"\{)|(\"\[)|(\]\")|(\\\\))','\1*#','g'),'(((:true)|(:false)|(:null)|([^A-Za-z]))\}\")','\1*#','g'),'\"*#', '"'), '"{*#', '{'),'"[*#','['),']"*#',']'),'\\*#','\'),'}"*#','}')
--在替代部分,\1标识的是第一个捕获组,就是第一个()包含的内容,本例中,是整个正则表达式,所以对}"场景,还单独判断的,因为他的前一个字符可能在前面的捕获组匹配过,所以要分解为2个regexp_place。
--捕获组 可以详细搜索看什么使用
- 点赞
- 收藏
- 关注作者
评论(0)