Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

  • 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
      }
    ]');
like image 675
zeisi Avatar asked Oct 17 '17 12:10

zeisi


People also ask

How do I query Jsonb data in PostgreSQL?

Querying the JSON document PostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.

Can you index Jsonb Postgres?

JSONB and Indexes PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by the GIN JSONB operator class.

Can Jsonb be an array?

jsonb[] is not an "extra" datatype, it's simply an array of JSONB values. Similar to text[] or integer[] . You can create arrays from every type.

Is Postgres Jsonb fast?

In the case of jsonb, it is comparatively faster to process the data as no reparsing is needed. JSON does not support indexing, unlike jsonb. Jsonb supports indexing to search for the keys or key/ value pairs which is a great advantage at a bigger database jsonb documents.


2 Answers

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';
like image 181
jlandercy Avatar answered Oct 19 '22 11:10

jlandercy


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

like image 29
zeisi Avatar answered Oct 19 '22 11:10

zeisi