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. CONQUERING
    JSONB IN
    POSTGRESQL
    Ines Panker

    View Slide

  2. Why did PostgreSQL add a
    JSON data type?

    View Slide

  3. 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

    View Slide

  4. 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
    );

    View Slide

  5. 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;
    }
    }

    View Slide

  6. 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

    View Slide

  7. "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

    View Slide

  8. How to query??

    View Slide

  9. 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

    View Slide

  10. How to query??
    (details)

    View Slide

  11. 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

    View Slide

  12. 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

    View Slide

  13. 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

    View Slide

  14. 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

    View Slide

  15. 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

    View Slide

  16. 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
    ...

    View Slide

  17. 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
    ...

    View Slide

  18. 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
    ...

    View Slide

  19. 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

    View Slide

  20. 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.

    View Slide

  21. What have we used JSON
    data types for so far?

    View Slide

  22. Shop++

    View Slide

  23. Change log

    View Slide

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

    View Slide

  25. Evet Recurence Rules

    View Slide

  26. REST API calls

    View Slide

  27. The End

    View Slide