CODEWITHSUNDEEP
postgresqljsonbpostgresql-9.6
zeisi
zeisi

Reputation: 5540

Querying Postgres 9.6 JSONB array of objects

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

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

Upvotes: 14

Views: 26880

Answers (3)

jian
jian

Reputation: 4824

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

Upvotes: 0

zeisi
zeisi

Reputation: 5540

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

Upvotes: 16

jlandercy
jlandercy

Reputation: 11002

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

Upvotes: 24

Related Questions