postgresql JSON Support Querying complex JSON documents

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

Taking a complex JSON document in a table:

CREATE TABLE mytable (data JSONB NOT NULL);
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);
INSERT INTO mytable VALUES($$
{
    "name": "Alice",
    "emails": [
        "[email protected]",
        "[email protected]"
    ],
    "events": [
        {
            "type": "birthday",
            "date": "1970-01-01"
        },
        {
            "type": "anniversary",
            "date": "2001-05-05"
        }
    ],
    "locations": {
        "home": {
            "city": "London",
            "country": "United Kingdom"
        },
        "work": {
            "city": "Edinburgh",
            "country": "United Kingdom"
        }
    }
}
$$);

Query for a top-level element:

SELECT data->>'name' FROM mytable WHERE data @> '{"name":"Alice"}';

Query for a simple item in an array:

SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["[email protected]"]}';

Query for an object in an array:

SELECT data->>'name' FROM mytable WHERE data @> '{"events":[{"type":"anniversary"}]}';

Query for a nested object:

SELECT data->>'name' FROM mytable WHERE data @> '{"locations":{"home":{"city":"London"}}}';

Performance of @> compared to -> and ->>

It is important to understand the performance difference between using @>, -> and ->> in the WHERE part of the query. Although these two queries appear to be broadly equivalent:

SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
SELECT data FROM mytable WHERE data->>'name' = 'Alice';

the first statement will use the index created above whereas the latter two will not, requiring a complete table scan.

It is still allowable to use the -> operator when obtaining resultant data, so the following queries will also use the index:

SELECT data->'locations'->'work' FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data->'locations'->'work'->>'city' FROM mytable WHERE data @> '{"name":"Alice"}';


Got any postgresql Question?