Encontrando uma agulha no JSON

Palestra realizada no PGConf.Brasil 2024 sobre buscas em colunas JSONB no PostgreSQL:

Agenda:
- Sintaxe, operadores e funções
- Exibir um JSON
- Buscas
- Índices

Transcript

1. Encontrando uma agulha no JSON
2. Encontrando uma agulha no JSON Agenda • Sintaxe, operadores e funções • Exibir um JSON • Buscas • Índices
3. Encontrando uma agulha no JSON Sintaxe, operadores e funções
4. 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
5. 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
6. 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}
7. 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;
8. 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
9. 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
10. Encontrando uma agulha no JSON Exibir um JSON
11. 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" ] }');
12. 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)
13. 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)
14. 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}
15. 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
16. 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
17. Encontrando uma agulha no JSON Buscas
18. 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)
19. 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'];
20. 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")';
21. 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)';
22. 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")';
23. 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")';
24. Encontrando uma agulha no JSON Índices
25. 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
26. 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
27. 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
28. 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
29. 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
30. 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
31. 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
32. 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
33. Dúvidas? Contatos: • Savepoint • Slides: Speaker decker (Fábio Telles) • Email: fabio@savepoint.inf.br • LinkedIn: Telles