PgSql jsonb类型查询

十年河东，十年河西，莫欺少年穷

学无止境，精益求精

json函数及操作，详情请参考：http://www.postgres.cn/docs/12/functions-json.html

表结构如下：

create table chargeing
(
id UUID primary key not null,
heart jsonb not null,    
createtime timestamp  not null
);
create index chargeing_heart_index on chargeing using gin(heart);

该表创建了索引，

一个为Jsonb类型的gin索引（注意：jsonb和json类型创建索引时，应使用 gin 类型索引）

数据表数据结构如下

其中，有如下结构json数据

{
  "n1": 1,
  "n2": 3,
  "n3": 4,
  "n4": 2,
  "csq": "28,99",
  "nos": "306051450,306050647,306050607,306050623,306050611,306050095,306051644,306050354,306051468,306050791,306051841,306051137,306051138,306050139,306050921,306050470,306051473,306050594,306051799,306051396,306050679,306050615,306050958,306050920,306050924",
  "sts": "2,5,6,6,4,6,3,6,6,6,6,4,6,6,6,5,3,6,6,1,4,6,4,6,3",
  "points": [
    {
      "ID": 7,
      "RI": 14,
      "RT": 23,
      "RV": 5323,
      "TI": 70,
      "TT": 39,
      "TV": 4761,
      "WH": 82,
      "typ": "",
      "RXSN": 1306080009,
      "TXSN": 306051644
    },
    {
      "ID": 17,
      "RI": 12,
      "RT": 23,
      "RV": 5267,
      "TI": 64,
      "TT": 44,
      "TV": 4788,
      "WH": 16,
      "typ": "",
      "RXSN": 1306080079,
      "TXSN": 306051473
    },
    {
      "ID": 25,
      "RI": 219,
      "RT": 36,
      "RV": 6686,
      "TI": 378,
      "TT": 40,
      "TV": 4638,
      "WH": 54,
      "typ": "",
      "RXSN": 2305310090,
      "TXSN": 306050924
    }
  ],
  "deviceNo": "C23009C0001"
}

 1、json 和 josnb 查询操作符

 2、jsonb 额外的操作符

函数

 

 

3、查询需求

3.1、查询points数组中的第一个元素

SELECT
 
heart -> 'points' ->> 0 as rx 
 
FROM
 
chargeing;