Slide 1
Slide 1 text
Encontrando uma agulha
no JSON
Slide 2
Slide 2 text
Encontrando uma agulha no JSON
Agenda
● Sintaxe, operadores e funções
● Exibir um JSON
● Buscas
● Índices
Slide 3
Slide 3 text
Encontrando uma agulha no JSON
Sintaxe, operadores e funções
Slide 4
Slide 4 text
Encontrando uma
agulha no JSON
Sintaxe, operadores e funções
json jsonb
Validação Validação
Armazenamento interno como TEXT Armazenamento interno como
binário
Preserva características originais do
JSON
Otimiza o formato original do JSON,
removendo espaços em branco,
formatos numéricos etc.
Sem otimização, operadores e
funções para busca
Otimização para busca e indexação
Slide 5
Slide 5 text
Encontrando uma
agulha no JSON
Sintaxe, operadores e funções
Sintaxe
● Caracteres: equivalente ao TEXT do Postgres, sempre envolvido
por aspas duplas
● Números: equivalente ao NUMERIC no Postgres
● Booleanos: equivalente ao BOOLEAN no Postgres, somente aceita
true ou false em letras minúsculas
● Nulo, só aceita null, em letras minúsculas
https://www.postgresql.org/docs/current/datatype-json.html
Slide 6
Slide 6 text
Encontrando uma
agulha no JSON
Sintaxe, operadores e funções
Validação
● SELECT 5::json;
ERROR: cannot cast type integer to json
● SELECT '5'::json;
json
------
5
● SELECT 'a'::json;
ERROR: invalid input syntax for type json
● SELECT '["a"]'::json;
json
-------
["a"]
● SELECT '{"a"}'::json;
ERROR: invalid input syntax for type json
● SELECT '{"a": null}'::json;
json
-------------
{"a": null}
Slide 7
Slide 7 text
Encontrando uma
agulha no JSON
Sintaxe, operadores e funções
Validação
● SELECT '[1, 2, "foo", null]'::json;
● SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
● SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
Slide 8
Slide 8 text
Encontrando uma
agulha no JSON
Operadores e funções
● Extrair dados:
○ jsonb_pretty
○ ->, ->>
○ #>, #>>
○ json_extract_path, json_extract_path_text
● Extrair dados como tabelas:
○ json_array_elements, json_array_elements_text
○ json_each, json_each_text
○ json_object_keys
○ json_to_record, json_to_recordset
○ json_table
https://www.postgresql.org/docs/current/functions-json.html
Slide 9
Slide 9 text
Encontrando uma
agulha no JSON
Operadores e funções
● Converter dados para JSON:
○ to_json
○ array_to_json
○ row_to_json
● Operadores boleanos para buscas
○ ?, ?| e ?&
○ @>, <@
○ @?, @@ (Jsonpath)
○ REGEX
https://www.postgresql.org/docs/current/functions-json.html
Slide 10
Slide 10 text
Encontrando uma agulha no JSON
Exibir um JSON
Slide 11
Slide 11 text
Encontrando uma
agulha no JSON
CREATE TABLE t (i serial, doc jsonb);
INSERT INTO t (doc) VALUES
('{
"guid":
"9c36adc1-7fb5-4d5b-83b4-90356a46061a",
"name": "Angela Barton",
"is_active": true,
"company": "Magnafone",
"address": "178 Howard Place, Gulf,
Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]
}');
Slide 12
Slide 12 text
Encontrando uma
agulha no JSON
jsonb_pretty
SELECT doc FROM t;
{"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", "name":
"Angela Barton", "tags": ["enim", "aliquip", "qui"], "address":
"178 Howard Place, Gulf, Washing
ton, 702", "company": "Magnafone", "latitude": 19.793713,
"is_active": true, "longitude": 86.513373, "registered":
"2009-11-07T08:53:22 +08:00"}
(1 row)
Slide 13
Slide 13 text
Encontrando uma
agulha no JSON
jsonb_pretty
SELECT jsonb_pretty(doc) FROM t;
jsonb_pretty
-----------------------------------------------------------
{ +
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", +
"name": "Angela Barton", +
"tags": [ +
"enim", +
"aliquip", +
"qui" +
], +
"address": "178 Howard Place, Gulf, Washington, 702",+
"company": "Magnafone", +
"latitude": 19.793713, +
"is_active": true, +
"longitude": 86.513373, +
"registered": "2009-11-07T08:53:22 +08:00" +
}
(1 row)
Slide 14
Slide 14 text
Encontrando uma
agulha no JSON
Operador ->
json (ARRAY) -> integer → json
jsonb (ARRAY) -> integer → jsonb
SELECT '["A", "B", "C", "D"]'::jsonb -> 3;
"D"
SELECT '[{"A": 1}, {"B": 2}, {"C": 3}, {"D": 4}]'::jsonb -> 2;
{"C": 3}
Slide 15
Slide 15 text
Encontrando uma
agulha no JSON
Operadores -> e ->>
json -> text → json
jsonb -> text → jsonb
SELECT doc -> 'name' FROM t;
"Angela Barton"
json ->> integer → text
jsonb ->> integer → text
SELECT doc ->> 'name' FROM t;
Angela Barton
Slide 16
Slide 16 text
Encontrando uma
agulha no JSON
Operadores #> e #>>
json #> text[] → json
jsonb #> text[] → jsonb
SELECT doc #> '{tags,2}' FROM t;
"qui"
json #>> text[] → text
jsonb #>> text[] → text
SELECT doc #>> '{tags,2}' FROM t;
qui
Slide 17
Slide 17 text
Encontrando uma agulha no JSON
Buscas
Slide 18
Slide 18 text
Encontrando uma
agulha no JSON
Operadores @> e <@
jsonb @> jsonb → boolean
jsonb <@ jsonb → boolean
SELECT i FROM T WHERE doc @> '{"name": "Angela Barton"}';
1
(1 row)
SELECT i FROM T WHERE '{"is_active": true}' <@ doc;
1
(1 row)
Slide 19
Slide 19 text
Encontrando uma
agulha no JSON
Operadores ?, ?| e ?&
jsonb ? text → boolean
jsonb ?| text[] → boolean
jsonb ?& text[] → boolean
SELECT i FROM t WHERE doc ? 'name';
SELECT i FROM t WHERE doc ?| ARRAY['name', 'city'];
SELECT i FROM t WHERE doc ?& ARRAY['name', 'city'];
Slide 20
Slide 20 text
Encontrando uma
agulha no JSON
Operadores @@ e @? (jsonpath)
jsonb @@ jsonpath → boolean
jsonb @? jsonpath → boolean
SELECT i FROM t
WHERE doc @@ '$.company == "Magnafone"';
SELECT i FROM t
WHERE doc @? '$.company ? (@ == "Magnafone")';
Slide 21
Slide 21 text
Encontrando uma
agulha no JSON
Operadores @@ e @? (jsonpath)
jsonb @@ jsonpath → boolean
jsonb @? jsonpath → boolean
SELECT i FROM t
WHERE doc @@ '$.latitude > 19';
SELECT i FROM t
WHERE doc @? '$.latitude ? (@ > 19)';
SELECT i FROM t
WHERE doc @? '$.latitude ? (@ > 19 && @ < 25)';
Slide 22
Slide 22 text
Encontrando uma
agulha no JSON
Operadores @@ e @? (jsonpath)
jsonb @@ jsonpath → boolean
jsonb @? jsonpath → boolean
SELECT i FROM t
WHERE doc @@ '$.tags[*] == "qui"';
SELECT i FROM t
WHERE doc @? '$.tags[*] ? (@ == "qui")';
SELECT i FROM t
WHERE doc @? '$.tags[*] ? (@ == "qui" || @ == "xpto")';
Slide 23
Slide 23 text
Encontrando uma
agulha no JSON
Operadores @@ e @? (jsonpath)
jsonb @@ jsonpath → boolean
jsonb @? jsonpath → boolean
SELECT i FROM t
WHERE doc @? '$.name ? (@ like_regex "A.*B")';
SELECT i FROM T
WHERE doc @? '$.name ? (@ like_regex "a.*b" flag "i")';
Slide 24
Slide 24 text
Encontrando uma agulha no JSON
Índices
Slide 25
Slide 25 text
Encontrando uma
agulha no JSON
Índices GIN
jsonb_ops (default) @> (jsonb,jsonb)
@? (jsonb,jsonpath)
@@ (jsonb,jsonpath)
? (jsonb,text)
?| (jsonb,text[])
?& (jsonb,text[])
jsonb_path_ops @> (jsonb,jsonb)
@? (jsonb,jsonpath)
@@ (jsonb,jsonpath)
https://www.postgresql.org/docs/current/gin.html#GIN-BUILTIN-OPCLASSES
Slide 26
Slide 26 text
Encontrando uma
agulha no JSON
Índices GIN
EXPLAIN ANALYZE SELECT * FROM tt
WHERE content @> '{"component": "Home"}';
QUERY PLAN
--------------------------------------------------------
-
Seq Scan on tt (cost=0.00..4160.15 rows=4 width=716)
(actual time=634.711..634.712 rows=0 loops=1)
Filter: (content @> '{"component": "Home"}'::jsonb)
Rows Removed by Filter: 39132
Planning Time: 0.461 ms
Execution Time: 634.733 ms
Slide 27
Slide 27 text
Encontrando uma
agulha no JSON
Índices GIN
CREATE INDEX ON tt USING GIN (content);
EXPLAIN ANALYZE SELECT * FROM tt
WHERE content @> '{"component": "Home"}';
QUERY
PLAN
---------------------------------------------------------------
---------------------------------------------------------
Bitmap Heap Scan on tt (cost=36.03..51.68 rows=4 width=716)
(actual time=0.387..0.387 rows=0 loops=1)
Recheck Cond: (content @> '{"component": "Home"}'::jsonb)
Rows Removed by Index Recheck: 4
Heap Blocks: exact=4
-> Bitmap Index Scan on tt_content_idx (cost=0.00..36.03
rows=4 width=0) (actual time=0.225..0.225 rows=4 loops=1)
Index Cond: (content @> '{"component":
"Home"}'::jsonb)
Planning Time: 1.234 ms
Execution Time: 0.427 ms
Slide 28
Slide 28 text
Encontrando uma
agulha no JSON
Btree + GIN
EXPLAIN ANALYZE SELECT * FROM tt
WHERE
company_id = 181748 AND
content @> '{"component": "root"}';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tt (cost=126.57..504.63 rows=109 width=716) (actual
time=0.600..0.868 rows=399 loops=1)
Recheck Cond: ((content @> '{"component": "root"}'::jsonb) AND
(company_id = 181748))
Heap Blocks: exact=107
-> BitmapAnd (cost=126.57..126.57 rows=109 width=0) (actual
time=0.577..0.578 rows=0 loops=1)
-> Bitmap Index Scan on tt_content_idx (cost=0.00..40.17
rows=556 width=0) (actual time=0.353..0.353 rows=564 loops=1)
Index Cond: (content @> '{"component": "root"}'::jsonb)
-> Bitmap Index Scan on tt_company_id_idx (cost=0.00..86.10
rows=7708 width=0) (actual time=0.215..0.215 rows=7788 loops=1)
Index Cond: (company_id = 181748)
Planning Time: 0.230 ms
Execution Time: 0.905 ms
Slide 29
Slide 29 text
Encontrando uma
agulha no JSON
Btree + GIN
CREATE EXTENSION btree_gin;
CREATE INDEX ON tt USING GIN(company_id, content);
EXPLAIN ANALYZE SELECT * FROM tt
WHERE
company_id = 181748 AND
content @> '{"component": "root"}';
QUERY PLAN
------------------------------------------------------------------------
--
Bitmap Heap Scan on tt (cost=53.12..431.18 rows=109 width=716) (actual
time=0.388..0.653 rows=399 loops=1)
Recheck Cond: ((company_id = 181748) AND (content @> '{"component":
"root"}'::jsonb))
Heap Blocks: exact=107
-> Bitmap Index Scan on tt_company_id_content_idx (cost=0.00..53.09
rows=109 width=0) (actual time=0.366..0.367 rows=399 loops=1)
Index Cond: ((company_id = 181748) AND (content @> '{"component":
"root"}'::jsonb))
Planning Time: 0.337 ms
Execution Time: 0.696 ms
https://www.postgresql.org/docs/current/btree-gin.html
Slide 30
Slide 30 text
Encontrando uma
agulha no JSON
Btree
CREATE INDEX ON tt ((content ->> 'component'::text));
EXPLAIN ANALYZE SELECT * FROM tt
WHERE content ->> 'component'::text = 'Home';
QUERY PLAN
---------------------------------------------------------------
Bitmap Heap Scan on tt (cost=5.81..642.05 rows=196 width=716)
(actual time=0.012..0.013 rows=0 loops=1)
Recheck Cond: ((content ->> 'component'::text) =
'Home'::text)
-> Bitmap Index Scan on tt_expr_idx (cost=0.00..5.76
rows=196 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((content ->> 'component'::text) =
'Home'::text)
Planning Time: 0.226 ms
Execution Time: 0.043 ms
Slide 31
Slide 31 text
Encontrando uma
agulha no JSON
pg_trgm
EXPLAIN ANALYZE SELECT * FROM tt
WHERE content::text LIKE '%Home%';
QUERY PLAN
--------------------------------------------------------
Seq Scan on tt (cost=0.00..7899.81 rows=313 width=734)
(actual time=4.091..2315.968 rows=1496 loops=1)
Filter: ((content)::text ~~ '%Home%'::text)
Rows Removed by Filter: 37636
Planning Time: 1.204 ms
Execution Time: 2316.179 ms
Slide 32
Slide 32 text
Encontrando uma
agulha no JSON
pg_trgm
CREATE EXTENSION pg_trgm;
CREATE INDEX ON tt USING GIN ((content::text) gin_trgm_ops);
EXPLAIN ANALYZE SELECT * FROM tt
WHERE content::text LIKE '%Home%';
QUERY PLAN
------------------------------------------------------------------------
--
Bitmap Heap Scan on tt (cost=30.43..1073.92 rows=313 width=734)
(actual time=2.215..870.238 rows=1496 loops=1)
Recheck Cond: ((content)::text ~~ '%Home%'::text)
Rows Removed by Index Recheck: 1734
Heap Blocks: exact=1157
-> Bitmap Index Scan on tt_content_idx1 (cost=0.00..30.35 rows=313
width=0) (actual time=0.590..0.591 rows=3230 loops=1)
Index Cond: ((content)::text ~~ '%Home%'::text)
Planning Time: 0.209 ms
Execution Time: 870.417 ms
https://www.postgresql.org/docs/current/pgtrgm.html
Slide 33
Slide 33 text
Dúvidas?
Contatos:
● Savepoint
● Slides: Speaker decker (Fábio Telles)
● Email: [email protected]
● LinkedIn: Telles