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

Conquering jsonb in PostgreSQL

inesp
April 22, 2017

Conquering jsonb in PostgreSQL

Recently PostgreSQL introduced the JSON and JSONB data types. At first, we ignored this news, we could not see how the JSON types could be of use to us. But then we used it once and then twice and then we almost went overboard with JSONs. This prompted us to stop, take a step back and analyze when a JSON type is useful and when it causes unnecessary hassle.

inesp

April 22, 2017
Tweet

Other Decks in Technology

Transcript

  1. Postgre SQL and NoSQL Postgre 9.4 (December 2014) JSONB: Binary

    format, slower input, faster processing JSON: Exact copy, same order Postgre 9.2 No explicit schema Key-Value pairs in JSON are equal to Key-Value pairs in columns
  2. CREATE TABLE "PaymentRequest" ( "ID" bigint PRIMARY KEY NOT NULL,

    "Created" timestamp without time zone, "LastEdited" timestamp without time zone, "Identifier" character varying(50), "FinalPrice" double precision, ..., "DeliveryTypeJSON" jsonb, "DeliveryAddressJSON" jsonb, "InvoiceAddressJSON" jsonb, "PromotionCodeJSON" jsonb, "PaymentJSON" jsonb); CREATE TABLE "PaymentRequestEntry" ( "ID" bigint PRIMARY KEY NOT NULL, "CartCount" integer DEFAULT 0, "PriceWithTax" double precision, "FullPriceWithTax" double precision, "PriceNoTax" double precision, "FullPriceNoTax" double precision, ..., "ShopProductJSON" jsonb, "PaymentRequestID" integer DEFAULT 0, "VoucherID" integer DEFAULT 0 );
  3. class ShopProduct{ private function DataForInvoice() { $data = array( 'SKU'

    => $this->SKU, 'Title' => $this->LocaleTitle(), 'Variant' => $this->Variant, 'PriceWithTax' => $this->PriceWithTax(), 'PriceNoTax' => $this->PriceNoTax(), 'DiscountPriceWithTax'=> $this->PriceWithTax(true), 'DiscountPriceNoTax' => $this->PriceNoTax(true), 'Promotion' => $this->Promotion()->DataForInvoice(), 'TaxRate' => $this->TaxRate($nice = false), 'TaxPriceOfOne' => $this->TaxOfOneProduct(), 'NoTax' => $this->NoTax(), 'AllowOneStepTransaction' => $this->AllowOneStepTransaction, 'IsVoucher' => $this->IsVoucher, .... ); return $data; } }
  4. INSERT INTO "PaymentRequestEntry" VALUES ( 1, 21.60, 24.00, 17.70, 19.67,

    ..., '{ "SKU":"LJCRD-24-ADL", "Title": "Visit Ljubljana Card, 24 hours", "Variant": "Adult", "PriceWithTax": 24.00, "PriceNoTax": 19.67, ..., "Promotion": { "Code": "SUMMER17", "DiscountPercent": 10.00, "DiscountAmount": null, "ActiveFrom": "2017-05-15 00:00:00" "ActiveTo": "2017-06-15 00:00:00" } "TaxRate": 0.22 }', 845623 ); ERROR: invalid input syntax for type json
  5. "ShopProductJSON" ----------------------------------------------- { "SKU": "LJCRD-24-ADL", "NoTax": false, "Title": "Visit Ljubljana

    Card, 24 hours", "TaxRate": 0.22, "Variant": "Adult", "IsVoucher": false, "Promotion": { "Code": "SUMMER17", "ActiveTo": "2017-06-15 00:00:00", "ActiveFrom": "2017-05-15 00:00:00", "DiscountAmount": null, "DiscountPercent": "10.00" }, "PriceNoTax": 19.67, "TaxRateNice": 0.22, "PriceWithTax": 24.00, "DiscountPriceNoTax": 17.70, "DiscountPriceWithTax": 21.60, "AllowOneStepTransaction": 1, ... } SELECT "ShopProductJSON" FROM "PaymentRequestEntry" WHERE "ID" = 1
  6. select "ID", "WholePriceWithTax", "NumberOfItems", "ShopProductJSON"->>'SKU' AS "SKU", "ShopProductJSON"->>'Title' as "Title",

    "ShopProductJSON"->>'PriceWithTax' as "PriceWithTax", "ShopProductJSON" from "PaymentRequestEntry" WHERE "PaymentRequestID" = 1 ID WholePrice WithTax NumberOf Items SKU Title PriceWith Tax 1 21.6 1 LJCRD-24-ADL Visit Ljubljana Card, 2 4 hours 24 2 90 2 TOUR-BIKE-24 Bike tour of Ljubljana 45 3 387 2 WC-17 WebCamp 2017 15
  7. JSON Operators: -> arrays objects int text SELECT '["a",2,"3"]'::json->0 as

    data data (json) ------------------ "a" SELECT '["a",2,"3"]'::json->1 as data data (json) ------------------ 2 SELECT '["a",2,"3"]'::json->2 as data data (json) ------------------ "3" SELECT '["a",2,"3"]'::json->'a' as data data (json) ------------------ SELECT '{"a":1,"b":2}'::json->'b' as data data (json) ------------------ 2 SELECT '{"a":1,"b":{"c":[1,2]}}'::json->'b' data (json) ------------------ {"c":[1,2]} json json
  8. JSON Operators: ->> arrays int SELECT '["a",2,"3"]'::json->>0 as data data

    (text) ------------------ "a" SELECT '["a",2,"3"]'::json->>1 > 1 ERROR: operator does not exist: text > integer SELECT ('["a",2,"3"]'::json->>1)::int > 1 data (boolean) --------------- t SELECT ('["a",2,"3"]'::json->1)::int > 1 ERROR: cannot cast type json to integer text
  9. JSON Operators: ->> SELECT '{"a":1,"b":"word"}'::json->>'b' data (text) ------------------ "word" SELECT

    '{"a":1,"b":"word"}'::json->>'b' = 'word' data(boolean) ----------- t SELECT '{ "identifier":1mo3j4, "items":[ { "mon":"Monday", "tue":"Tuesday" }, { "jan":"january", "feb":"february" } ] }'::json->'items'->0->>'tue' data (text) -------------- "Tuesday" objects text text
  10. JSON Operators: #>, #>> SELECT '{ "id":1, "items":[ { "mon":"Monday",

    "tue":"Tuesday" }, { "jan":"january", "feb":"february" } ] }'::json #>> '{items,0,tue}' data (text) -------------- "Tuesday" SELECT '{ "id":1, "items":[ { "mon":"Monday", "tue":"Tuesday" }, { "jan":"january", "feb":"february" } ] }'::json ->'items'->0->>'tue' data (text) -------------- "Tuesday" objects array of text text
  11. JSONB Operators: @>, ? SELECT '{ "promotion": { "code":"SUMMER17", "discount":"10"

    } }'::jsonb ? 'promotion' SELECT '{ "promotion": { "code":"SUMMER17", "discount":"10" } }'::jsonb->'promotion' ? 'code' SELECT '{ "id":1, "promotion": { "code":"SUMMER17", "discount":"10" } }'::jsonb @> '{"promotion":{"code": "SUMMER17"}}' data (boolean) -------------- t objects text boolean objects jsonb boolean
  12. SELECT count("ID") as number_of_products_promo_was_used FROM "PaymentRequestEntry" WHERE "ShopProductJSON" @> '{"Promotion":{"Code":

    "SUMMER17"}}' { "SKU": "LJCRD-24-ADL", "IsVoucher": false, "Promotion": { "Code": "SUMMER17", }, "PriceNoTax": "19.67", "TaxRateNice": "0.22", "PriceWithTax": "24.00", "DiscountPriceNoTax": "10.00", "DiscountPriceWithTax": "10.00", } SELECT "PaymentRequestID", sum(("ShopProductJSON"->>'PriceWithTax')::float) - sum(("ShopProductJSON"->>'DiscountPriceWithTax')::float) as money_saved FROM "PaymentRequestEntry" WHERE "ShopProductJSON" @> '{"Promotion":{"Code": "SUMMER17"}}‘ GROUP BY "PaymentRequestID" PaymentRequestID money_saved 845623 5.12 845622 6.01 845625 2.2 ...
  13. SELECT SUM("CartCount") AS number_of_products, SUM(("ShopProductJSON"->>'PriceWithTax')::float) - SUM(("ShopProductJSON"->>'DiscountPriceWithTax')::float) AS money_saved, r."DeliveryAddressJSON"->>'Country'

    AS country FROM "PaymentRequestEntry" e INNER JOIN "PaymentRequest" r ON r."ID" = e."PaymentRequestID" WHERE "ShopProductJSON"->'Promotion'->>'Code' = 'SUMMER17' GROUP BY r."DeliveryAddressJSON"->>'Country' ORDER BY money_saved number_of_products money_saved country 481 1503.72 DE 512 1438.56 AT 503 1289.05 IT ...
  14. SELECT sp."CalculatedPrice", e."ShopProductJSON"->>'PriceWithTax' as bought_price, e."Created" FROM "PaymentRequestEntry" e INNER

    JOIN "ShopProduct" sp ON sp."SKU" = e."ShopProductJSON"->>'SKU' AND e."ShopProductJSON" @> '{"SKU": "LJCRD-24-ADL"}' ORDER BY e."Created" desc CalculatedPrice bought_price Created 24 24 2017-04-21 15:23:01 24 21.6 2017-04-21 15:22:12 ... 24 22 2016-12-01 07:17:53 ...
  15. EXPLAIN SELECT * FROM "PaymentRequestEntry" WHERE "ShopProductJSON" @> '{"Promotion":{"Code": "SUMMER17"}}'

    Seq Scan on "PaymentRequestEntry" (cost=0.00..21.02 rows=3 width=100) Filter: ((("ShopProductJSON" -> 'Promotion'::text) ->> 'Code'::text) = 'SUMMER17'::text) GIN index over the WHOLE JSON for only 1 key in the JSON jsonb_ops : bigger & more versatily jsonb_path_ops: smaller & supports only @> querys
  16. EXPLAIN SELECT * FROM "PaymentRequestEntry" WHERE "ShopProductJSON" @> '{"Promotion":{"Code": "SUMMER17"}}'

    Seq Scan on "PaymentRequestEntry" (cost=0.00..511091.51 rows=3662 width=85) table "PaymentRequestEntry" size: 3635 MB, 2.807.147 rows CREATE index json_index ON "PaymentRequestEntry" USING GIN (("ShopProductJSON"->'Promotion'->'Code')) CREATE index json_index ON "PaymentRequestEntry" USING GIN ("ShopProductJSON") @> <@ ? ?| ?& CREATE index json_index ON "PaymentRequestEntry" USING GIN ("ShopProductJSON" jsonb_path_ops) @> indexname num_rows index_size json_index_ops 2.807.147 10 MB json_index_path_ops 2.807.147 7.832 MB (-20%) json_index_column 2.807.147 3.112 MB (-70%) json_index_column_path 2.807.147 3.112 MB (-70%) 1. 2. 3.
  17. JSON to Form { "type":"object", "title":"Comment", "properties":{ "name":{ "title":"Name", "type":"string"

    }, "email":{ "title":"Email", "type":"string", "pattern":"^\\S+@\\S+$", "description":"Email will be used for evil." }, "comment":{ "title":"Comment", "type":"string", "maxLength":20, "validationMessage":"Don't be greedy!" } }, "required":[ "name", "email", "comment" ] }