PostgreSQL之Json类型使用总结

待查询数据库“json”字段内容
表名：tablename
字段名：json

{
“CXX”: “北京市”,
“CRE”: “1”,
“CEE”: “oop”,
“IEOE”: [
{
“AAA”: “2882”,
“BBB”: “1”,
“CCC”: null,
“DDD”: “1245”,
“EEE”: -1,
“FFF”: 1
},
{
“AAA2”: “123”,
“BBB”: null,
“CCC”: “123456”,
“DDD”: “1234567”,
“EEE”: -1,
“FFF”: 1
}
],
“CFFFF”: “5555”,
“CGGGG”: “8”,
“CJJJJ”: “2323”,
“COOOO”: “23”,
“CFSDF”: “333”,
}

如果想要查询CXX的内容:

SELECT json->>’CXX’ as cxx FROM tablename;

查询IEOE下面的AAA的内容:

SELECT (json->’IEOE’)#>>’{0,AAA}’ as aaa FROM tablename ;

查询IEOE下面的AAA2的内容:
SELECT (json->’IEOE’)#>>’{1,AAA2}’ as aaa FROM tablename ;

当json字段里面的内容要作为where条件查询时，同样道理：
SELECT (json->’IEOE’)#>>’{1,AAA2}’ as aaa FROM tablename WHERE ( (json->’IEOE’)#>>’{1,AAA2}’) = ‘……’;