Share
Embed
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
Fábio Telles Rodriguez
November 07, 2024
Transcript
33 slides
-
Encontrando uma agulha no JSON Agenda • Sintaxe, operadores e
funções • Exibir um JSON • Buscas • Índices -
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 -
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 -
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} -
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; -
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 -
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 -
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" ] }'); -
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) -
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) -
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} -
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 -
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 -
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) -
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']; -
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")'; -
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)'; -
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")'; -
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")'; -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
Dúvidas? Contatos: • Savepoint • Slides: Speaker decker (Fábio Telles)
• Email: [email protected] • LinkedIn: Telles