准备

表结构

CREATE TABLE "public"."tmp_json" (
"before_records" text
);

数据

{
    "110000": {
        "code": "110000",
        "name": "北京市",
        "children": {
            "110101": {
                "code": "110101",
                "name": "东城区"
            },
            "110102": {
                "code": "110102",
                "name": "西城区"
            }
        }
    },
    "120000": {
        "code": "120000",
        "name": "天津市",
        "children": {
            "120101": {
                "code": "120101",
                "name": "和平区"
            },
            "120102": {
                "code": "120102",
                "name": "河东区"
            }
        }
    }
}

函数

text转json

SELECT before_records::json FROM tmp_json;

image.png

json_each

已","分割json

SELECT json_each(before_records::json) FROM tmp_json;

image.png

to_json

增加key,value:形式方便取值

SELECT to_json(json_each(before_records::json)) FROM tmp_json;

image.png

-> 键值对名称

通过->键值对名称取json值

SELECT to_json(json_each(before_records::json))->'value' FROM tmp_json;

image.png
参考链接:https://www.cnblogs.com/alianbog/p/5658156.html