PG/Guass Json处理样例

举报
张谱继 发表于 2024/06/04 20:41:37 2024/06/04
【摘要】 Json相关函数 http://www.postgres.cn/docs/9.4/functions-json.html关键点:操作符右操作数的类型描述示例示例结果->int获取JSON数组元素(索引从0开始)'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2{"c":"baz"}->text通过秘钥获取JSON对象字段'{"a": {"b":"...

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。
--捕获组 可以详细搜索看什么使用
【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。