Querying Postgres 9.6 JSONB array of objects

Question

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):

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?

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

jlandercy · Accepted Answer · 2017-10-17T12:39:42.577

19

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

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

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';

edited Oct 17 '17 at 12:39

answered Oct 17 '17 at 12:33

jlandercy

4,820
1
26
46

You can use [jsonb_array_elements](https://www.postgresql.org/docs/current/static/functions-json.html) and avoid cast. – Michel Milezzi Oct 17 '17 at 12:38

score 13 · Answer 2 · answered Oct 17 '17 at 12:45

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:

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

See also Postgresql query array of objects in JSONB field

Querying Postgres 9.6 JSONB array of objects

2 Answers2

Linked