查詢複雜的 JSON 文件

Created: November-22, 2018

在表中獲取複雜的 JSON 文件：

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": [
        "alice1@test.com",
        "alice2@test.com"
    ],
    "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"
        }
    }
}
$$);

查詢頂級元素：

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

查詢陣列中的簡單項：

SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["alice1@test.com"]}';

查詢陣列中的物件：

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

查詢巢狀物件：

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

`@>` 的效能與 `->` 和 `->>` 相比

重要的是要理解在查詢的 WHERE 部分中使用 @>，-> 和 ->> 之間的效能差異。雖然這兩個查詢似乎大致相同：

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

第一個語句將使用上面建立的索引，而後兩個不會，需要完整的表掃描。

在獲取結果資料時仍然允許使用 -> 運算子，因此以下查詢也將使用索引：

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

@> 的效能與 -> 和 ->> 相比

`@>` 的效能與 `->` 和 `->>` 相比