查询Postgres 9.6 JSONB对象数组 [英] Querying Postgres 9.6 JSONB array of objects

问题描述

我有下表:

CREATE TABLE trip
(
    id SERIAL PRIMARY KEY ,
    gps_data_json jsonb NOT NULL
);

gps_data_json中的JSON包含一个带有以下字段的旅行对象数组(以下示例数据):

The JSON in gps_data_json contains an array of of trip objects with the following fields (sample data below):

• 模式
• 时间戳
• 纬度
• 经度

我正在尝试获取包含某个模式"的所有行.

I'm trying to get all rows that contain a certain "mode".

SELECT * FROM trip
where gps_data_json ->> 'mode' = 'WALK';

我敢肯定我使用的->>运算符是错误的，但是我不确定谁能告诉查询JSONB字段是对象数组吗?

I pretty sure I'm using the ->> operator wrong, but I'm unsure who to tell the query that the JSONB field is an array of objects?

样本数据:

INSERT INTO trip (gps_data_json) VALUES
  ('[
      {
        "latitude": 47.063480377197266,
        "timestamp": 1503056880725,
        "mode": "TRAIN",
        "longitude": 15.450349807739258
      },
      {
        "latitude": 47.06362533569336,
        "timestamp": 1503056882725,
        "mode": "WALK",
        "longitude": 15.450264930725098
      }
    ]');

INSERT INTO trip (gps_data_json) VALUES
  ('[
      {
        "latitude": 47.063480377197266,
        "timestamp": 1503056880725,
        "mode": "BUS",
        "longitude": 15.450349807739258
      },
      {
        "latitude": 47.06362533569336,
        "timestamp": 1503056882725,
        "mode": "WALK",
        "longitude": 15.450264930725098
      }
    ]');

推荐答案

出现问题是因为->>运算符无法遍历数组:

The problem arises because ->> operator cannot walk through array:

• 首先使用json_array_elements函数取消嵌套json数组；
• 然后使用运算符进行过滤.

• First unnest your json array using json_array_elements function;
• Then use the operator for filtering.

以下查询可解决问题:

WITH 
A AS (
SELECT
    Id
   ,jsonb_array_elements(gps_data_json) AS point
FROM trip
)
SELECT *
FROM A
WHERE (point->>'mode') = 'WALK';

这篇关于查询Postgres 9.6 JSONB对象数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！