Querying complex JSON documents
suggest changeTaking 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": [
"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 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":["alice1@test.com"]}';
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"}';
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents