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-17 12:39:42Z

24

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, 2017 at 12:39

answered Oct 17, 2017 at 12:33

jlandercy

11.6k3 gold badges48 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michel Milezzi Over a year ago

You can use jsonb_array_elements and avoid cast.

zeisi · Accepted Answer · 2017-10-17 12:45:04Z

16

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

answered Oct 17, 2017 at 12:45

zeisi

5,5492 gold badges25 silver badges22 bronze badges

Comments

jian · Accepted Answer · 2021-09-30 06:53:48Z

0

select *  from
    (select id, jsonb_array_elements(gps_data_json) point from trip where id = 16) t
where point @> '{"mode": "WALK"}';

In My Table, id = 16 is to make sure that the specific row is jsonb-array datatype ONLY. Since other rows data is just JSONB object. So you must filter out jsonb-array data FIRST. Otherwise : ERROR: cannot extract elements from an object

answered Sep 30, 2021 at 6:53

jian

5,0231 gold badge29 silver badges46 bronze badges

Collectives™ on Stack Overflow

Querying Postgres 9.6 JSONB array of objects

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related