postgresql 查询 Postgres 9.6 JSONB 对象数组

Querying Postgres 9.6 JSONB array of objects

提问by zeisi

I have the following table:

我有下表：

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

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

gps_data_json 中的 JSON 包含一组具有以下字段的旅行对象（下面的示例数据）：

• mode
• timestamp
• latitude
• longitude

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

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

我正在尝试获取包含特定“模式”的所有行。

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

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?

我很确定我使用的 ->> 运算符是错误的，但我不确定谁告诉查询 JSONB 字段是一个对象数组？

Sample data:

样本数据：

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
      }
    ]');

回答by jlandercy

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

出现问题是因为->>操作符无法遍历数组：

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

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

Following query does the trick:

以下查询可以解决问题：

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

回答by zeisi

Unnesting the array works fine, if you only want the objects containing the values queried. The following checks for containment and returns the full JSONB:

如果您只想要包含查询值的对象，则取消嵌套数组可以正常工作。以下检查是否包含并返回完整的 JSONB：

SELECT * FROM trip
WHERE gps_data_json @> '[{"mode": "WALK"}]';

See also Postgresql query array of objects in JSONB field

另请参阅JSONB 字段中的对象的 Postgresql 查询数组