PostgreSQL JSON类型

Postgres支持两种JSON类型: json和jsonb. 两种类型在数据存储前都作了JSON格式的检查,但jsonb存储的是已分解的二进制格式,所以存储的时候效率稍微有些低,但在数据处理的时候可以显著提升效率。同时jsonb还支持索引,有一些函数只适用于jsonb类型。所以推荐使用jsonb。json格式如下:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

索引:

默认的GIN索引(即jsonb_ops)支持的运算符包括?, ?&, ?|, @>;而jsonb_path_ops只支持@>

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops); # default jsonb_ops
可以使用WHERE jdoc @> ‘{“company”: “Magnafone”}’ 进行查询.
我们虽然在jdoc建了索引,但?运算符不能直接应用于jdoc列上,如WHERE jdoc -> ‘tags’ ? ‘qui’,需要按如下方式建索引:
CREATE INDEX idxgintags ON api USING GIN ((jdoc -> ‘tags’))
也可以不用改索引,只需要改写查询方法为:WHERE jdoc @> ‘{“tags”: [“qui”]}’ 即可。

jsonb_path_ops索引比jsonb_ops更小,性能更好。但搜索的documents包含空的json结构时会走全索引扫描。

查询:

1.简单查询:

pipeline=# select * from test_json where data_value->>'group_id'='10000AB' and data_value->>'data_time'='2018-04-11 16:29:55'
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 13.17, "LG": 11310.29823, "LT": 2301.69026, "LAT": 23.0255422, "LGD": 1.0, "LNG": 113.1770455, "LTD": 3.0, "SPD": 0.0, "KP01": 1010.1, "KS01": 54.43, "KW01": 30.92, "KPM10": 69.0, "KPM25": 58.0}, "group_id": "10000AB", "data_time": "2018-04-11 16:29:55"}
(1 row)

pipeline=# select * from test_json where data_value @> '{"group_id":"10000AB"}'::jsonb and data_value @> '{"data_time":"2018-04-11 16:29:55"}'::jsonb;
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 13.17, "LG": 11310.29823, "LT": 2301.69026, "LAT": 23.0255422, "LGD": 1.0, "LNG": 113.1770455, "LTD": 3.0, "SPD": 0.0, "KP01": 1010.1, "KS01": 54.43, "KW01": 30.92, "KPM10": 69.0, "KPM25": 58.0}, "group_id": "10000AB", "data_time": "2018-04-11 16:29:55"}
(1 row)

pipeline=# select * from test_json where data_value @> '{"group_id":"10000AB"}'::jsonb and data_value->'metrics'->>'LG' = '11310.40213';
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 13.15, "LG": 11310.40213, "LT": 2301.23897, "LAT": 23.0180141, "LGD": 1.0, "LNG": 113.1787735, "LTD": 3.0, "SPD": 16.0, "KP01": 1010.23, "KS01": 56.48, "KW01": 30.56, "KPM10": 72.0, "KPM25": 63.0}, "group_id": "10000AB", "data_time": "2018-04-11 16:36:22"}
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 13.21, "LG": 11310.40213, "LT": 2301.2153, "LAT": 23.0176195, "LGD": 1.0, "LNG": 113.1787734, "LTD": 3.0, "SPD": 30.0, "KP01": 1014.6, "KS01": 79.64, "KW01": 26.92, "KPM10": 59.0, "KPM25": 48.0}, "group_id": "10000AB", "data_time": "2018-05-11 22:24:41"}
(2 rows)

2.json块批量匹配

pipeline=# select * 
pipeline-# from test_json a
pipeline-# where coalesce(data_value->>'data_time','') >= '2018-04-14 00:00:00'
pipeline-#   and coalesce(data_value->>'data_time','') <= '2018-04-14 23:59:59'
pipeline-#   and exists (
pipeline(#       select 1 from jsonb_each_text(a.data_value->'metrics') s 
pipeline(#       where coalesce(s.value::numeric(21,5),0)<0 or coalesce(s.value::numeric(21,5),0)>100000
pipeline(# );
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KB03": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 12.92, "LG": 11308.99402, "LT": 2303.859, "LAT": 23.0617431, "LGD": 1.0, "LNG": 113.1553501, "LTD": 3.0, "SPD": 0.0, "KB03": 82.0, "KS01": 0.01, "KW01": -40.34, "KPM10": 64.0, "KPM25": 52.0}, "group_id": "20000AB", "data_time": "2018-04-14 17:20:47"}
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KB03": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 12.92, "LG": 11308.9941, "LT": 2303.86771, "LAT": 23.0618883, "LGD": 1.0, "LNG": 113.1553514, "LTD": 3.0, "SPD": 12.0, "KB03": 79.0, "KS01": 0.01, "KW01": -40.34, "KPM10": 64.0, "KPM25": 51.0}, "group_id": "20000AB", "data_time": "2018-04-14 17:21:08"}
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KB03": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 12.92, "LG": 11308.99404, "LT": 2303.89722, "LAT": 23.0623804, "LGD": 1.0, "LNG": 113.1553504, "LTD": 3.0, "SPD": 22.0, "KB03": 88.0, "KS01": 0.01, "KW01": -40.34, "KPM10": 64.0, "KPM25": 50.0}, "group_id": "20000AB", "data_time": "2018-04-14 17:21:19"}
 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KB03": null, "KS01": null, "KW01": null, "KPM10": null, "KPM25": null}, "metrics": {"BV": 12.92, "LG": 11308.9518, "LT": 2303.91172, "LAT": 23.0626233, "LGD": 1.0, "LNG": 113.1546476, "LTD": 3.0, "SPD": 30.0, "KB03": 87.0, "KS01": 0.01, "KW01": -40.34, "KPM10": 63.0, "KPM25": 50.0}, "group_id": "20000AB", "data_time": "2018-04-14 17:21:30"}
(4 rows)

3.数据按列显示

pipeline=# select a.data_value->>'group_id' as group_id,
pipeline-#        a.data_value->>'data_time' as data_time,
pipeline-#        b.key as model_code,b.value,c.value as status
pipeline-# from test_json a, 
pipeline-#      jsonb_each_text(a.data_value->'metrics') b, 
pipeline-#      jsonb_each_text(a.data_value->'status') c 
pipeline-# where data_value->>'group_id'='10000AB' 
pipeline-# and data_value->>'data_time'='2018-04-11 16:29:55'
pipeline-# and b.key=c.key;
    group_id     |      data_time      | model_code |    value    | status 
----------------+---------------------+------------+-------------+--------
 10000AB | 2018-04-11 16:29:55 | BV         | 13.17       | 
 10000AB | 2018-04-11 16:29:55 | LG         | 11310.29823 | 
 10000AB | 2018-04-11 16:29:55 | LT         | 2301.69026  | 
 10000AB | 2018-04-11 16:29:55 | LAT        | 23.0255422  | 
 10000AB | 2018-04-11 16:29:55 | LGD        | 1.0         | 
 10000AB | 2018-04-11 16:29:55 | LNG        | 113.1770455 | 
 10000AB | 2018-04-11 16:29:55 | LTD        | 3.0         | 
 10000AB | 2018-04-11 16:29:55 | SPD        | 0.0         | 
 10000AB | 2018-04-11 16:29:55 | KP01       | 1010.1      | 
 10000AB | 2018-04-11 16:29:55 | KS01       | 54.43       | 
 10000AB | 2018-04-11 16:29:55 | KW01       | 30.92       | 
 10000AB | 2018-04-11 16:29:55 | KPM10      | 69.0        | 
 10000AB | 2018-04-11 16:29:55 | KPM25      | 58.0        | 
(13 rows)

4.数据按行显示

pipeline=# select to_timestamp(s.data_value->>'data_time','yyyy-MM-dd HH24:mi:ss') as data_time,
pipeline-#        s.data_value->>'group_id'::varchar(32) as group_id, 
pipeline-#        "BV" as bv_value, "LAT" as lat_value, "LG" as lg_value, "LGD" as lgd_value, 
pipeline-#        "LT" as lt_value, "LTD" as ltd_value, "SPD" as spd_value, "KB01" as kb01_value
pipeline-# from test_json s,
pipeline-#      jsonb_to_record(s.data_value->'metrics') as x(
pipeline(#         "BV" numeric(21,5), "LAT" numeric(21,5), "LG" numeric(21,5), "LGD" numeric(21,5),
pipeline(#         "LT" numeric(21,5), "LTD" numeric(21,5), "SPD" numeric(21,5), "KB01" numeric(21,5)
pipeline(#     )
pipeline-# where data_value->>'group_id'='10000AB' 
pipeline-# and data_value->>'data_time'='2018-04-11 16:29:55';
       data_time        |    group_id     | bv_value | lat_value |  lg_value   | lgd_value |  lt_value  | ltd_value | spd_value | kb01_value 
------------------------+----------------+----------+-----------+-------------+-----------+------------+-----------+-----------+------------
 2018-04-11 16:29:55+08 | 10000AB | 13.17000 |  23.02554 | 11310.29823 |   1.00000 | 2301.69026 |   3.00000 |   0.00000 |           
(1 row)

--或
pipeline=# select a.data_value->>'group_id' as group_id,
pipeline-#        a.data_value->>'data_time' as data_time,
pipeline-#        a.data_value->'metrics'->>'BV' as bv_value,
pipeline-#        a.data_value->'metrics'->>'LAT' as lat_value,
pipeline-#        a.data_value->'metrics'->>'LG' as lg_value,
pipeline-#        a.data_value->'metrics'->>'LGD' as lgd_value,
pipeline-#        a.data_value->'metrics'->>'LT' as lt_value,
pipeline-#        a.data_value->'metrics'->>'LTD' as ltd_value,
pipeline-#        a.data_value->'metrics'->>'SPD' as spd_value,
pipeline-#        a.data_value->'metrics'->>'KB01' as kb01_value
pipeline-# from test_json a
pipeline-# where data_value->>'group_id'='10000AB' 
pipeline-# and data_value->>'data_time'='2018-04-11 16:29:55';
    group_id     |      data_time      | bv_value | lat_value  |  lg_value   | lgd_value |  lt_value  | ltd_value | spd_value | kb01_value 
----------------+---------------------+----------+------------+-------------+-----------+------------+-----------+-----------+------------
 10000AB | 2018-04-11 16:29:55 | 13.17    | 23.0255422 | 11310.29823 | 1.0       | 2301.69026 | 3.0       | 0.0       | 
(1 row)

5.对数组处理

postgres=# select data_value from test_json;
 {"tag": [{"dev": "B280V3", "USAGE": "001"}], 
  "status": [{"BV": "N", "LG": "N", "LT": "N", "LGD": "N", "LTD": "N", "SPD": "N", "KB03": "N", "KP01": "N", "KS01": "N", "KW01": "N", "KPM10": "N", "KPM25": "N"}], 
  "metrics": [{"BV": 12.00, "LG": null, "LT": null, "LGD": null, "LTD": 4, "SPD": 0, "KB03": 51, "KP01": 1015.27, "KS01": 63.42, "KW01": 24.84, "KPM10": 47, "KPM25": 35}], 
  "group_id": "30000AB", "data_time": "20180228110759"
 }          
postgres=# select j.data_value->>'data_time' as data_time, 
postgres-#        j.data_value->>'group_id'::varchar(32) as group_id,
postgres-#        x.*
postgres-# from test_json j, 
postgres-#      jsonb_to_recordset(j.data_value->'metrics') as x(
postgres(#        BV numeric(21,5), 
postgres(#        LG numeric(21,5),
postgres(#        LT numeric(21,5),
postgres(#        LGD numeric(21,5),
postgres(#        LTD numeric(21,5),
postgres(#        SPD numeric(21,5),
postgres(#        KB03 numeric(21,5),
postgres(#        KP01 numeric(21,5),
postgres(#        KS01 numeric(21,5),
postgres(#        KW01 numeric(21,5),
postgres(#        KPM10 numeric(21,5),
postgres(#        KPM25 numeric(21,5)
postgres(#      );
   data_time    |    group_id     |    BV    | LG | LT | LGD |   LTD   |   SPD   |   KB03   |    KP01    |   KS01   |   KW01   |  KPM10   |  KPM25   
----------------+----------------+----------+----+----+-----+---------+---------+----------+------------+----------+----------+----------+----------
 20180228110759 | 30000AB | 12.00000 |    |    |     | 4.00000 | 0.00000 | 51.00000 | 1015.27000 | 63.42000 | 24.84000 | 47.00000 | 35.00000

--或
postgres=# create table group_value ( 
            BV numeric(21,5), 
            LG numeric(21,5),
            LT numeric(21,5),
            LGD numeric(21,5),
            LTD numeric(21,5),
            SPD numeric(21,5),
            KB03 numeric(21,5),
            KP01 numeric(21,5),
            KS01 numeric(21,5),
            KW01 numeric(21,5),
            KPM10 numeric(21,5),
            KPM25 numeric(21,5)
           );
CREATE TABLE
postgres=# select * from group_value;
 BV | LG | LT | LGD | LTD | SPD | KB03 | KP01 | KS01 | KW01 | KPM10 | KPM25 
----+----+----+-----+-----+-----+------+------+------+------+-------+-------
(0 rows)   
postgres=# select j.data_value->>'data_time' as data_time, 
postgres-#        j.data_value->>'group_id'::varchar(32) as group_id,
postgres-#        x.*
postgres-# from test_json j, 
postgres-#      jsonb_populate_recordset(null::"group_value",j.data_value->'metrics') x;
   data_time    |    group_id     |    BV    | LG | LT | LGD |   LTD   |   SPD   |   KB03   |    KP01    |   KS01   |   KW01   |  KPM10   |  KPM25   
----------------+----------------+----------+----+----+-----+---------+---------+----------+------------+----------+----------+----------+----------
 20180228110759 | 30000AB | 12.00000 |    |    |     | 4.00000 | 0.00000 | 51.00000 | 1015.27000 | 63.42000 | 24.84000 | 47.00000 | 35.00000

6.对数组处理2

postgres=# select data_value from test_json;
{"tag": {"dev": "B280V3", "usage": "001"}, 
 "metrics": [{"value": null, "status": "N", "model_code": "LG"}, 
             {"value": null, "status": "N", "model_code": "LGD"}, 
             {"value": 4, "status": "N", "model_code": "LT"}, 
             {"value": 4, "status": "N", "model_code": "LTD"}, 
             {"value": 0, "status": "N", "model_code": "SPD"}, 
             {"value": 12.00, "status": "N", "model_code": "BV"}, 
             {"value": 35, "status": "N", "model_code": "KPM25"}, 
             {"value": 47, "status": "N", "model_code": "KPM10"}, 
             {"value": 51, "status": "N", "model_code": "KB03"}, 
             {"value": 24.84, "status": "N", "model_code": "KW01"}, 
             {"value": 63.42, "status": "N", "model_code": "KS01"}, 
             {"value": 1015.27, "status": "N", "model_code": "KP01"}], 
 "group_id": "40000AB", 
 "data_time": "20180228110759"
}

postgres=# create table model_value2 (model_code varchar(100), value numeric(21,5), status char(2));
CREATE TABLE
postgres=# select j.data_value->>'data_time' as data_time, 
postgres-#        j.data_value->>'group_id'::varchar(32) as group_id,
postgres-#        x.*
postgres-# from test_json j, 
postgres-#      jsonb_populate_recordset(null::"model_value2",j.data_value->'metrics') x;
   data_time    |    group_id     | model_code |   value    | status 
----------------+----------------+------------+------------+--------
 20180228110759 | 40000AB | LG         |            | N 
 20180228110759 | 40000AB | LGD        |            | N 
 20180228110759 | 40000AB | LT         |    4.00000 | N 
 20180228110759 | 40000AB | LTD        |    4.00000 | N 
 20180228110759 | 40000AB | SPD        |    0.00000 | N 
 20180228110759 | 40000AB | BV         |   12.00000 | N 
 20180228110759 | 40000AB | KPM25      |   35.00000 | N 
 20180228110759 | 40000AB | KPM10      |   47.00000 | N 
 20180228110759 | 40000AB | KB03       |   51.00000 | N 
 20180228110759 | 40000AB | KW01       |   24.84000 | N 
 20180228110759 | 40000AB | KS01       |   63.42000 | N 
 20180228110759 | 40000AB | KP01       | 1015.27000 | N 
(12 rows)

--或

postgres=# select j.data_value->>'data_time' as data_time, 
postgres-#        j.data_value->>'group_id'::varchar(32) as group_id,
postgres-#        j.data_value->'metrics'->x->>'model_code' as model_code,
postgres-#        j.data_value->'metrics'->x->>'value' as value,
postgres-#        j.data_value->'status'->x->>'status' as status
postgres-# from test_json j, 
postgres-#      generate_series(0,jsonb_array_length(j.data_value->'metrics')-1) x;
   data_time    |    group_id     | model_code |  value  | status 
----------------+----------------+------------+---------+--------
 20180228110759 | 40000AB | LG         |         | 
 20180228110759 | 40000AB | LGD        |         | 
 20180228110759 | 40000AB | LT         | 4       | 
 20180228110759 | 40000AB | LTD        | 4       | 
 20180228110759 | 40000AB | SPD        | 0       | 
 20180228110759 | 40000AB | BV         | 12.00   | 
 20180228110759 | 40000AB | KPM25      | 35      | 
 20180228110759 | 40000AB | KPM10      | 47      | 
 20180228110759 | 40000AB | KB03       | 51      | 
 20180228110759 | 40000AB | KW01       | 24.84   | 
 20180228110759 | 40000AB | KS01       | 63.42   | 
 20180228110759 | 40000AB | KP01       | 1015.27 | 
(12 rows)

修改

1.增删改:

jsonb_set(target jsonb, path text[],new_value jsonb[,create_missing boolean])
create_missing =true 匹配不到时就创建,可以匹配到是就替换;=false 匹配不到时不作任何操作

--"LG": 11310.29823
pipeline=# select * from test_json where data_value->>'mn_code'='88888800001246' and data_value->>'data_time'='2018-04-11 16:29:55';

 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": 
null, "KPM25": null}, "metrics": {"BV": 13.17, "LG": 11310.29823, "LT": 2301.69026, "LAT": 23.0255422, "LGD": 1.0, "LNG": 113.1770455, "LTD": 3.0, "SPD": 0.0, "KP01": 1010.1, "KS01": 54.43, "KW01": 30.92, "K
PM10": 69.0, "KPM25": 58.0}, "mn_code": "88888800001246", "data_time": "2018-04-11 16:29:55"}
(1 row)

--"LG": 100,
pipeline=# select jsonb_set(data_value, '{"metrics","LG"}', '100', true) from test_json where data_value->>'mn_code'='88888800001246' and data_value->>'data_time'='2018-04-11 16:29:55';

 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": 
null, "KPM25": null}, "metrics": {"BV": 13.17, "LG": 100, "LT": 2301.69026, "LAT": 23.0255422, "LGD": 1.0, "LNG": 113.1770455, "LTD": 3.0, "SPD": 0.0, "KP01": 1010.1, "KS01": 54.43, "KW01": 30.92, "KPM10": 6
9.0, "KPM25": 58.0}, "mn_code": "88888800001246", "data_time": "2018-04-11 16:29:55"}
(1 row)

--"LG": 11310.29823
pipeline=# select * from test_json where data_value->>'mn_code'='88888800001246' and data_value->>'data_time'='2018-04-11 16:29:55';

 {"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": 
null, "KPM25": null}, "metrics": {"BV": 13.17, "LG": 11310.29823, "LT": 2301.69026, "LAT": 23.0255422, "LGD": 1.0, "LNG": 113.1770455, "LTD": 3.0, "SPD": 0.0, "KP01": 1010.1, "KS01": 54.43, "KW01": 30.92, "K
PM10": 69.0, "KPM25": 58.0}, "mn_code": "88888800001246", "data_time": "2018-04-11 16:29:55"}

2.删除键

pipeline=# select data_value #- '{metrics,LG}' from test_json where data_value->>'mn_code'='88888800001246' and data_value->>'data_time'='2018-04-11 16:29:55';

{"tag": {"dev": "B280V3", "USAGE": "001"}, "status": {"BV": null, "LG": null, "LT": null, "LAT": null, "LGD": null, "LNG": null, "LTD": null, "SPD": null, "KP01": null, "KS01": null, "KW01": null, "KPM10": 
null, "KPM25": null}, "metrics": {"BV": 13.17, "LT": 2301.69026, "LAT": 23.0255422, "LGD": 1.0, "LNG": 113.1770455, "LTD": 3.0, "SPD": 0.0, "KP01": 1010.1, "KS01": 54.43, "KW01": 30.92, "KPM10": 69.0, "KPM25
": 58.0}, "mn_code": "88888800001246", "data_time": "2018-04-11 16:29:55"}
(1 row)

参考

PostgreSQL官方文档–>FUNCTIONS-JSONB-OP-TABLE章节

©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页