使用postgreSQL的json/jsonb特有操作符查询与传统字符串比较查询的对比

在PostgreSQL9.5中引入了新的JSONB功能，以下是常规方式和jsonb方式的对比。

假设数据表"test"有以下数据 

id	info
1	{"id": 4434, "des": "", "icon": "", "name": "model", "type": 5, "unit": null, "mtime": 1526522782397, "domain": null, "uitype": "Text", "caption": "型号", "defaultValue": null}
2	[{"id": "7404", "des": "执行结果成功与否", "icon": "", "name": "Result", "type": 1, "unit": null, "mtime": "1548231914408", "domain": null, "uitype": "Number", "caption": "执行结果", "defaultValue": null}]
3	[{"fid": 5435, "name": "osm_id", "value": "400767621"}, {"fid": 5436, "name": "code", "value": "5122"}, {"fid": 5437, "name": "fclass", "value": "residential"}, {"fid": 4113, "name": "name", "value": ""}, {"fid": -1, "name": "ref", "value": ""}, {"fid": -1, "name": "oneway", "value": "B"}, {"fid": -1, "name": "maxspeed", "value": "0"}, {"fid": -1, "name": "layer", "value": "0"}, {"fid": -1, "name": "bridge", "value": "F"}, {"fid": -1, "name": "tunnel", "value": "F"}]

如果要查询数据表id为3的info值中fid为5437的值

 

常规方式：

1.后台查询数据库 SELECT * FROM "test" WHERE ID = 3

2.解析json字符串（假如使用了fastjson）：

public class TestService {
    public static void main(String[] args){
        String json = "[{\"fid\": 5435, \"name\": \"osm_id\", \"value\": \"400767621\"}, {\"fid\": 5436, \"name\": \"code\", \"value\": \"5122\"}, {\"fid\": 5437, \"name\": \"fclass\", \"value\": \"residential\"}, {\"fid\": 4113, \"name\": \"name\", \"value\": \"\"}, {\"fid\": -1, \"name\": \"ref\", \"value\": \"\"}, {\"fid\": -1, \"name\": \"oneway\", \"value\": \"B\"}, {\"fid\": -1, \"name\": \"maxspeed\", \"value\": \"0\"}, {\"fid\": -1, \"name\": \"layer\", \"value\": \"0\"}, {\"fid\": -1, \"name\": \"bridge\", \"value\": \"F\"}, {\"fid\": -1, \"name\": \"tunnel\", \"value\": \"F\"}]";
        dealJson(json);
    }

    public static void dealJson(String json){
        JSONArray objects = JSON.parseArray(json);
        objects.stream().forEach(obj ->{
            JSONObject jsonObject = JSON.parseObject(obj.toString());
            int fid = (int) jsonObject.get("fid");
            if (fid == 5437){
                System.out.println(obj.toString());
                return;
            }
        });
    }

得到的结果如下：{"fid":5437,"name":"fclass","value":"residential"}

 

 

postgresql的jsonb方式：

查询数据库：SELECT * FROM (SELECT t.* FROM test, jsonb_to_recordset(info) AS t(fid BIGINT, name text,value text) WHERE test.id=3) x WHERE fid = 5437;

这样就能直接查到相应的值。