postgresql Interrogazione di documenti JSON complessi


Esempio

Prendere un documento JSON complesso in una tabella:

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"
        }
    }
}
$$);

Query per un elemento di primo livello:

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

Query per un elemento semplice in un array:

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

Query per un oggetto in una matrice:

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

Query per un oggetto nidificato:

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

Prestazioni di @> rispetto a -> e ->>

È importante capire la differenza di prestazioni tra l'utilizzo di @> , -> e ->> nella parte WHERE della query. Sebbene queste due query sembrino essere sostanzialmente equivalenti:

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

la prima istruzione utilizzerà l'indice creato sopra mentre le ultime due non lo faranno, richiedendo una scansione completa della tabella.

È ancora possibile utilizzare l'operatore -> quando si ottengono i dati risultanti, quindi le seguenti query utilizzeranno anche l'indice:

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