postgresql解析json

解析table1中字段名为rule的json字符串，字符串内容如下：

{
    "a":99,
    "b":"string",
    "c":[
        {
            "c1":9,
            "c2":[
                6,
                6,
                7
            ]
        }
    ],
    "d":"2022-04-01 10:57:15",
    "e":40,
    "f":[
        "39,6,9"
    ],
    "g":"0",
    "h":"0",
    "i":2066,
    "j":"ib",
    "k":"lj"
}

查询c1:

SELECT json_array_elements(rule::json -> 'c') ->> 'c1' as c from table1

查询c2，并将数组拆开：

SELECT UNNEST(CAST(replace(replace(
								(json_array_elements(rule::json -> 'c') ->> 'c2')::VARCHAR,
                        '[', '{'  ), ']', '}' ) AS int[])) as aa
        from table1