Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Encontrando uma agulha no JSON

Avatar for Fábio Telles Rodriguez Fábio Telles Rodriguez
November 07, 2024
120

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

Avatar for Fábio Telles Rodriguez

Fábio Telles Rodriguez

November 07, 2024
Tweet

Transcript

  1. Encontrando uma agulha no JSON Agenda • Sintaxe, operadores e

    funções • Exibir um JSON • Buscas • Índices
  2. 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
  3. 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
  4. 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}
  5. 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;
  6. 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
  7. 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
  8. 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" ] }');
  9. 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)
  10. 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)
  11. 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}
  12. 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
  13. 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
  14. 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)
  15. 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'];
  16. 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")';
  17. 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)';
  18. 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")';
  19. 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")';
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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