Introducing
Your new presentation assistant.
Refine, enhance, and tailor your content, source relevant images, and edit visuals quicker than ever before.
Trending searches
CREATE EXTENSION hstore;
SELECT 'a=>1,a=>2'::hstore;
hstore
----------
"a"=>"1"
(1 row)
SELECT 'author => "Monty Python", sketch => Spam'::hstore -> 'author' AS author;
author
--------------
Monty Python
(1 row)
JSONB type performance in PostgreSQL 9.4
marco.nenciarini@2ndquadrant.it
@mnencia
mnencia
www.2ndQuadrant.it
Copyright (c) 2012-2015, 2ndQuadrant
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License
select xmlelement(name MeetUs,
xmlattributes(
'Milan' as location,
'12 May 2015' as date
),
'Interesting people');
xmlelement
---------------------------------------------------------------------------
<meetus location="Milan" date="13 May 2015">
Interesting people
</meetus>
SELECT query_to_xml($$SELECT id, name FROM sketches$$,
true, false, '');
query_to_xml
---------------------------------------------------------------
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<id>1</id>
<name>Dead Parrot</name>
</row>
<row>
<id>2</id>
<name>Spam</name>
</row>
</table>
Details: http://www.postgresql.org/docs/9.4/static/functions-xml.html
SELECT xpath('/table/row[id/text()=1]/name/text()', $$
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<id>1</id>
<name>Dead Parrot</name>
</row>
<row>
<id>2</id>
<name>Spam</name>
</row>
</table>
$$);
xpath
-----------------
{"Dead Parrot"}
jsonb @> jsonb
Containment
recursively convert a value to JSON, using (type based):
to_json(anyelement)
jsonb ? text
Key existence
'{"a":1,"b":2}'::jsonb @> '{"a":1}'
jsonb = jsonb
Equality
aggregates values as a JSON array
json_agg(record)
'{"a":1,"b":2}'::jsonb ? 'a'
'{"a":1,"b":2}'::jsonb = '{"b":2,"a":1}'
Details: http://www.postgresql.org/docs/9.4/static/function-json.html
Details: http://www.postgresql.org/docs/9.4/static/hstore.html
Details: http://www.postgresql.org/docs/9.4/static/functions-json.html
'[1,2,3]'::json->2
hstore -> text
get value for key
'{"a":1,"b":2}'::json->'b'
'{"a":1,"b":2}'::jsonb @> '{"a":1}'
'{"a": {"b":2}}'::json->'{a,b}'
hstore ? text
does hstore contain key?
jsonb @> jsonb
Containment
'a=>1,b=>2'::hstore -> 'a'
hstore @> hstore
does left operand contain right?
CREATE INDEX ON mydata USING GIN (jcol jsonb_path_ops);
Supports GIN and GIST indexes
'a=>1,b=>2'::hstore ? 'b'
JSON -> int
Get JSON array element (indexed from zero)
Details: http://www.postgresql.org/docs/9.4/static/function-json.html
JSON -> text
Get JSON object field by key
The >> variant returns the result as text
JSON #> text[]
Get JSON object at specified path
'a=>1,b=>2'::hstore @> 'a=>1'
Details: http://www.postgresql.org/docs/9.4/static/functions-json.html
Details: http://www.postgresql.org/docs/9.4/static/hstore.html
XML
With two '>'
hstore
CREATE TABLE reviews (review hstore);
COPY reviews FROM 'customer_reviews_1998.hstore';
COPY 589859
Time: 7348.897 ms
Customer review data from Amazon for the year 1998
JSON
SELECT round(avg((review->'review_rating')::numeric), 2)
FROM reviews WHERE review -> 'product_category' = 'Home & Garden';
round
-------
4.59
(1 row)
Time: 148.975 ms
http://examples.citusdata.com/customer_reviews_nested_1998.json.gz
CREATE INDEX ON reviews USING gin(review);
CREATE INDEX
Time: 64761.753 ms
SELECT round(avg((review->'review_rating')::numeric), 2)
FROM reviews
WHERE review @> 'product_category => "Home & Garden"';
round
-------
4.59
(1 row)
Time: 7.642 ms
CREATE TABLE reviews(review json);
COPY reviews FROM 'customer_reviews_nested_1998.json';
COPY 589859
Time: 5360.982 ms
CREATE TABLE reviews(review jsonb);
COPY reviews FROM customer_reviews_nested_1998.json';
COPY 589859
Time: 8905.210 ms
SELECT round(avg((review#>>'{review,rating}')::numeric), 2)
FROM reviews
WHERE review#>>'{product,category}' = 'Home & Garden';
round
-------
4.59
(1 row)
Time: 2342.235 ms
SELECT round(avg((review#>>'{review,rating}')::numeric), 2)
FROM reviews
WHERE review#>>'{product,category}' = 'Home & Garden';
round
-------
4.59
(1 row)
Time: 235.420 ms
CREATE INDEX on reviews ((review#>>'{product,category}'));
CREATE INDEX
Time: 14697.687 ms
SELECT round(avg((review#>>'{review,rating}')::numeric), 2) FROM reviews WHERE review#>>'{product,category}' = 'Home & Garden';
round
-------
4.59
(1 row)
Time: 19.650 ms
CREATE INDEX on reviews USING gin(review);
CREATE INDEX
Time: 17825.265 ms
SELECT round(avg((review#>>'{review,rating}')::numeric), 2)
FROM reviews
WHERE review@>'{"product": {"category": "Home & Garden"}}';
round
-------
4.59
(1 row)
Time: 13.982 ms
CREATE INDEX on reviews USING gin(review jsonb_path_ops);
CREATE INDEX
Time: 6709.042 ms
SELECT round(avg((review#>>'{review,rating}')::numeric), 2)
FROM reviews
WHERE review@>'{"product": {"category": "Home & Garden"}}';
round
-------
4.59
(1 row)
Time: 8.423 ms