SlideShare a Scribd company logo
1 of 43
Download to read offline
PostgreSQL 9.4 and JSON
Andrew Dunstan
andrew@dunslane.net
andrew.dunstan@pgexperts.com
Overview
● What is JSON? Why use JSON?
● Quick review of 9.2, 9.3 features
● 9.4 new features
● Future work
What is JSON?
● Data serialization format
– rfc 7158, previously rfc 4627
● Lightweight
● Human readable
● Becoming ubiquitous
● Simpler and more compact than XML
What it looks like
{
"books" : [
{ "title": "Catch 22”, "author": "Joseph Heller"},
{ "title": "Catcher in the Rye", "author": "J. D. Salinger"}
],
"publishers": [
{ "name": "Random House" },
{ "name": "Penguin" }
],
"active": true,
"version": 35,
"date": "2003-09-13",
“reference”: null
}
Scalars:
● quoted strings
● numbers
● true, false, null
No extensions
No date/time types
Why use it?
● Everyone is moving that way
● Understood everywhere there is a JavaScript
interpreter
– Especially browsers
● ... and in a large number of other languages
– e.g. Perl, Python
● node.js is becoming very widely used
● More compact than XML
● Most applications don't need the richer
structure of XML
Why not use it?
● Overly verbose
● Field names are repeated
● Arguably less readable than, say, YAML
● Not suitable for huge objects
● Not quite type rich enough
– No timestamp support
Review – pre 9.2 facilities
Nothing – store JSON as text
● No validation
● No JSON production
● No JSON extraction
Review – 9.2 data type
New JSON type
● Stored as text
● Reasonably performant state-based
validating parser
● Kudos: Robert Haas
Review – 9.2 production functions
● Turn non-JSON data into JSON
● row_to_json(anyrecord)
● array_to_json(anyarray)
● Optional second param for pretty printing
● My humble contribution ☺
What's missing?
● JSON production features are incomplete
● JSON processing is totally absent
● Have to use PLV8, PLPerl or some such
9.3 Features – JSON production
● to_json(any)
● Can be used on any datum, not just
arrays and records
● json_agg(record)
● Much faster than
array_to_json(array_agg(record))
9.3 and casts to JSON
● Production functions honor casts to JSON
for non-builtin types
● Not needed for builtins, as we know
how to convert them
● Saves syscache lookups where we
know it's not necessary
● Is this wise, or necessary?
– Counter case is ISO 8601 Timestamps
– Workaround – use to_char()
9.3 hstore and JSON
● hstore_to_json(hstore)
● Also used as a cast function
● hstore_to_json_loose(hstore)
● Uses heuristics about whether or not
certain possibly numeric and boolean
values need to be quoted.
9.3 JSON parser rewrite
● New parser uses recursive descent
pattern
● Caller can supply event handlers for
certain events
● c.f. XML SAX parsers
● Validator uses NULL handlers for all
events
● Tokenizing routines of previous parser
largely kept
9.3 JSON processing functions
● All leverage new parser API
● Operators give a more natural style to
extraction operations
● Many have two forms, producing either
JSON output, which can be further
processed, or text output, which cannot.
● Text output is de-escaped and
dequoted
9.3 extraction operators (1)
● -> fetch an array element or object
member as json
● '[4,5,6]'::json->2 ⟹ 6
– json arrays are 0 based, unlike SQL
arrays
● '{"a":1,"b":2}'::json->'b' ⟹ 2
9.3 extraction operators (2)
● ->> fetch an array element orobject
member as text
● '["a","b","c"]'::json->2 ⟹ c
– Instead of "c"
9.3 extraction operators (3)
● #> and #>> fetch data pointed at by a
path
● Path is an array of text elements
● Treats arrays correctly by some trying to
treat path element as an integer of
necessary
● '{"a":[6,7,8]}'::json#>'{a,1}' ⟹ 7
●
9.3 extraction functions
● json_extract_path(json, VARIADIC
path_elems text[]);
● json_extract_path_text(json, VARIADIC
path_elems text[]);
● Same as #> and #>> operators, but
you can pass the path as a variadic
array
● json_extract_path('{"a":[6,7,8]}','a','1')
⟹ 7
9.3 turn JSON into records
● CREATE TYPE x AS (a int, b int);
● SELECT * FROM
json_populate_record(null::x,
'{"a":1,"b":2}', false);
● SELECT * FROM
json_populate_recordset(null::x,'[{"a":1,"
b":2},{"a":3,"b":4}]', false);
9.3 turn JSON into key/value pairs
● SELECT * FROM
json_each('{"a":1,"b":"foo"}')
● SELECT * FROM
json_each_text('{"a":1,"b":"foo"}')
● Deliver columns named “key” and
“value”
9.3 get keys from JSON object
● SELECT * FROM
json_object_keys('{"a":1,"b":"foo"}')
9.3 JSON array processing
● SELECT json_array_length('[1,2,3,4]');
● SELECT * FROM
json_array_elements('[1,2,3,4]')
9.3 API extension example
● Code can be cloned from
https://bitbucket.org/adunstan/json_typeof
● See also jsonfuncs.c for lots of examples of
use.
What's missing in 9.3?
● Efficiency
● Richer querying
● Canonicalization
● Indexing
● Complete Utilities for building json
● CRUD operations
9.4 JSON features
● New json creation functions
● New utility functions
● jsonb type
– Efficient operations
– Indexable
– Canonical
9.4 Features – new json aggregate
● json_object_agg(“any”, “any”)
● Turn a set of key value pairs int a json object
● Select json_object_agg(name, population)
from cities;
● { “Smallville”: 300, “Metropolis”: 1000000}
9.4 Features – json creation functions
● json_build_object( VARIADIC “any”)
● json_build_array(VARIADIC “any”)
● json_object(text[])
● json_object(keys text[], values text[])
9.4 json creation simple examples
● select json_build_object('a',1,'b',true)
● {“a”: 1, “b”: true}
● select json_build_array('a',1,'b',true)
● [“a”, 1, “b”, true]
● select json_object(array['a','b','c','d']
● Or select json_object(array[['a','b'],['c','d']]
● Or select json_object(array['a','c'],array['b','d'])
● {“a”:”b”, “c”:”d”}
9.4 json creation complex example
select json_build_object(
'a', json_build_array('b',false,'c',99),
'd', json_build_object('e',array[9,8,7]::int[],
'f', (select to_json(r) from (
select relkind, oid::regclass as name
from pg_class where relname = 'pg_class') r)),
'g', json_object(array[['w','x'],['y','z']]));
{"a" : ["b", false, "c", 99], "d" : {"e" : [9,8,7], "f" : {"relkind":"r","name
":"pg_class"}}, "g" : {"w" : "x", "y" : "z"}}
9.4 features – json_typeof
● json_typeof(json) returns text
– Result is one of:
● 'object'
● 'array'
● 'string'
● 'number'
● 'boolean'
● 'null'
● Null
● Kudos: Andrew Tipton
9.4 features – jsonb type
● Accepts the same inputs as json
– Uses the 9.3 parsing API
– Checks Unicode escapes, especially use of
surrogate pairs, more thoroughly than json.
● Representation closely mirrors json syntax
9.4 Features jsonb kudos
● Originally grew out of work on nested hstore
– Major kudos to Oleg Bartunov, Teodor
Sigaev, Alexander Korotkov
– Adaptation of indexable operators by Peter
Geoghegan
– Most of parser, and implementation of json
functions and operators for jsonb by moi
9.4 Features – jsonb canonical
representation
● Whitespace and punctuation dissolved away
● Only one value per object key is kept
– Last one wins.
– Key order determined by length, then
bytewise comparison
9.4 Features – jsonb operators
● Has the json operators with the same
semantics:
-> ->> #> #>>
● Has standard equality and inequality operators
= <> > < >= <=
● Has new operations testing containment,
key/element presence
@> <@ ? ?| ?&
9.4 Features – jsonb equality and
inequality
● Comparison is piecewise
– Object > Array > Boolean > Number > String > Null
– Object with n pairs > object with n - 1 pairs
– Array with n elements > array with n - 1 elements
● Not particularly intuitive
● Not ECMA standard ordering, which is possibly not
suitable anyway
9.4 features – jsonb functions
● jsonb has all the json processing functions,
with the same semantics
– i.e. functions that take json arguments
– Function names start with jsonb_ instead of
json_
● jsonb does not have any of the json creation
functions
– i.e. functions that take non-json arguments
and output json
– Workaround: cast result to jsonb
9.4 features – jsonb indexing
● For more details see Oleg, Teodor and
Alexander's Vodka talk from yesterday.
● 2 ops classes for GIN indexes
– Default supports contains and exists
operators:
@> ? ?& ?|
– Non-default ops class jsonb_path_ops only
supports @> operator
– Faster
– Smaller indexes
9.4 features – jsonb subdocument
indexes
● Use “get” operators to construct expression
indexes on subdocument:
– CREATE INDEX author_index ON books
USING GIN ((jsondata -> 'authors'));
– SELECT * FROM books
WHERE jsondata -> 'authors' ? 'Carl
Bernstein';
When to use json, when jsonb
● If you need any of these, use json
– Storage of validated json, without processing
or indexing it
– Preservation of white space in json text
– Preservation of object key order
– Preservation of duplicate object keys
– Maximum input/output speed
● For any other case, use jsonb
Future of JSON in PostgreSQL
● More indexing options
– Vodka!
– Further requirements will emerge from use
● Json alteration operations
– e.g. Set a field, or delete an element
● General document store
– Can we get around the “rewrite a whole
datum” issue
Unconference issues
● Statistics?
● Planner support?
● ???
Questions?

More Related Content

What's hot

Debugging & Tuning in Spark
Debugging & Tuning in SparkDebugging & Tuning in Spark
Debugging & Tuning in SparkShiao-An Yuan
 
Etl confessions pg conf us 2017
Etl confessions   pg conf us 2017Etl confessions   pg conf us 2017
Etl confessions pg conf us 2017Corey Huinker
 
Scalable XQuery Processing with Zorba on top of MongoDB
Scalable XQuery Processing with Zorba on top of MongoDBScalable XQuery Processing with Zorba on top of MongoDB
Scalable XQuery Processing with Zorba on top of MongoDBWilliam Candillon
 
Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku
Postgres & Redis Sitting in a Tree- Rimas Silkaitis, HerokuPostgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku
Postgres & Redis Sitting in a Tree- Rimas Silkaitis, HerokuRedis Labs
 
Extending Apache Spark – Beyond Spark Session Extensions
Extending Apache Spark – Beyond Spark Session ExtensionsExtending Apache Spark – Beyond Spark Session Extensions
Extending Apache Spark – Beyond Spark Session ExtensionsDatabricks
 
Toro DB- Open-source, MongoDB-compatible database, built on top of PostgreSQL
Toro DB- Open-source, MongoDB-compatible database,  built on top of PostgreSQLToro DB- Open-source, MongoDB-compatible database,  built on top of PostgreSQL
Toro DB- Open-source, MongoDB-compatible database, built on top of PostgreSQLInMobi Technology
 
PostgreSQL WAL for DBAs
PostgreSQL WAL for DBAs PostgreSQL WAL for DBAs
PostgreSQL WAL for DBAs PGConf APAC
 
Introduction to PostgreSQL
Introduction to PostgreSQLIntroduction to PostgreSQL
Introduction to PostgreSQLJim Mlodgenski
 
Getting by with just psql
Getting by with just psqlGetting by with just psql
Getting by with just psqlCorey Huinker
 
High Availability PostgreSQL with Zalando Patroni
High Availability PostgreSQL with Zalando PatroniHigh Availability PostgreSQL with Zalando Patroni
High Availability PostgreSQL with Zalando PatroniZalando Technology
 
Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...
Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...
Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...Ontico
 
Meet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike Steenbergen
Meet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike SteenbergenMeet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike Steenbergen
Meet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike Steenbergendistributed matters
 
Webscale PostgreSQL - JSONB and Horizontal Scaling Strategies
Webscale PostgreSQL - JSONB and Horizontal Scaling StrategiesWebscale PostgreSQL - JSONB and Horizontal Scaling Strategies
Webscale PostgreSQL - JSONB and Horizontal Scaling StrategiesJonathan Katz
 
Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...
Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...
Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...DataStax Academy
 
Pgbr 2013 postgres on aws
Pgbr 2013   postgres on awsPgbr 2013   postgres on aws
Pgbr 2013 postgres on awsEmanuel Calvo
 

What's hot (20)

memcached Distributed Cache
memcached Distributed Cachememcached Distributed Cache
memcached Distributed Cache
 
Debugging & Tuning in Spark
Debugging & Tuning in SparkDebugging & Tuning in Spark
Debugging & Tuning in Spark
 
PostgreSQL Terminology
PostgreSQL TerminologyPostgreSQL Terminology
PostgreSQL Terminology
 
Etl confessions pg conf us 2017
Etl confessions   pg conf us 2017Etl confessions   pg conf us 2017
Etl confessions pg conf us 2017
 
Scalable XQuery Processing with Zorba on top of MongoDB
Scalable XQuery Processing with Zorba on top of MongoDBScalable XQuery Processing with Zorba on top of MongoDB
Scalable XQuery Processing with Zorba on top of MongoDB
 
Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku
Postgres & Redis Sitting in a Tree- Rimas Silkaitis, HerokuPostgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku
Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku
 
Pgbr 2013 fts
Pgbr 2013 ftsPgbr 2013 fts
Pgbr 2013 fts
 
Extending Apache Spark – Beyond Spark Session Extensions
Extending Apache Spark – Beyond Spark Session ExtensionsExtending Apache Spark – Beyond Spark Session Extensions
Extending Apache Spark – Beyond Spark Session Extensions
 
Toro DB- Open-source, MongoDB-compatible database, built on top of PostgreSQL
Toro DB- Open-source, MongoDB-compatible database,  built on top of PostgreSQLToro DB- Open-source, MongoDB-compatible database,  built on top of PostgreSQL
Toro DB- Open-source, MongoDB-compatible database, built on top of PostgreSQL
 
PostgreSQL WAL for DBAs
PostgreSQL WAL for DBAs PostgreSQL WAL for DBAs
PostgreSQL WAL for DBAs
 
Introduction to PostgreSQL
Introduction to PostgreSQLIntroduction to PostgreSQL
Introduction to PostgreSQL
 
Getting by with just psql
Getting by with just psqlGetting by with just psql
Getting by with just psql
 
High Availability PostgreSQL with Zalando Patroni
High Availability PostgreSQL with Zalando PatroniHigh Availability PostgreSQL with Zalando Patroni
High Availability PostgreSQL with Zalando Patroni
 
Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...
Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...
Полнотекстовый поиск в PostgreSQL за миллисекунды (Олег Бартунов, Александр К...
 
Really Big Elephants: PostgreSQL DW
Really Big Elephants: PostgreSQL DWReally Big Elephants: PostgreSQL DW
Really Big Elephants: PostgreSQL DW
 
Meet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike Steenbergen
Meet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike SteenbergenMeet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike Steenbergen
Meet Spilo, Zalando’s HIGH-AVAILABLE POSTGRESQL CLUSTER - Feike Steenbergen
 
3 apache-avro
3 apache-avro3 apache-avro
3 apache-avro
 
Webscale PostgreSQL - JSONB and Horizontal Scaling Strategies
Webscale PostgreSQL - JSONB and Horizontal Scaling StrategiesWebscale PostgreSQL - JSONB and Horizontal Scaling Strategies
Webscale PostgreSQL - JSONB and Horizontal Scaling Strategies
 
Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...
Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...
Cassandra Summit 2014: Reading Cassandra SSTables Directly for Offline Data A...
 
Pgbr 2013 postgres on aws
Pgbr 2013   postgres on awsPgbr 2013   postgres on aws
Pgbr 2013 postgres on aws
 

Viewers also liked

DEKRA Magazine - n. 5 del 2010
DEKRA Magazine - n. 5 del 2010DEKRA Magazine - n. 5 del 2010
DEKRA Magazine - n. 5 del 2010DEKRA Italia
 
EXPOSICION LAS RSS EN LA EDUCACION
EXPOSICION LAS RSS EN LA EDUCACIONEXPOSICION LAS RSS EN LA EDUCACION
EXPOSICION LAS RSS EN LA EDUCACIONAdrian Acosta Alzate
 
DEKRA Magazine - n. 9 del 2011
DEKRA Magazine - n. 9 del 2011DEKRA Magazine - n. 9 del 2011
DEKRA Magazine - n. 9 del 2011DEKRA Italia
 
PostgreSQL 9.3 and JSON - talk at PgOpen 2013
PostgreSQL 9.3 and JSON - talk at PgOpen 2013PostgreSQL 9.3 and JSON - talk at PgOpen 2013
PostgreSQL 9.3 and JSON - talk at PgOpen 2013Andrew Dunstan
 
PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...
PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...
PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...pgdayrussia
 
Pei Autonoma SocializacióN Pra El Vicerrector
Pei Autonoma SocializacióN   Pra El VicerrectorPei Autonoma SocializacióN   Pra El Vicerrector
Pei Autonoma SocializacióN Pra El Vicerrectormariogeopolitico
 
PostgreSQL Blackhole FDW - lightning talk 2013
PostgreSQL Blackhole FDW - lightning talk 2013PostgreSQL Blackhole FDW - lightning talk 2013
PostgreSQL Blackhole FDW - lightning talk 2013Andrew Dunstan
 
Peru Marketing Symposium 2012 David Rogers
Peru Marketing Symposium 2012 David RogersPeru Marketing Symposium 2012 David Rogers
Peru Marketing Symposium 2012 David RogersDavid Rogers
 
Reinventing Marketing for B2B Services - One North Conference
Reinventing Marketing for B2B Services - One North ConferenceReinventing Marketing for B2B Services - One North Conference
Reinventing Marketing for B2B Services - One North ConferenceDavid Rogers
 
Flexible Indexing with Postgres
Flexible Indexing with PostgresFlexible Indexing with Postgres
Flexible Indexing with PostgresEDB
 
Indexing Complex PostgreSQL Data Types
Indexing Complex PostgreSQL Data TypesIndexing Complex PostgreSQL Data Types
Indexing Complex PostgreSQL Data TypesJonathan Katz
 
B trees and_b__trees
B trees and_b__treesB trees and_b__trees
B trees and_b__treesmeghu123
 
Histología del sistema óseo
Histología del sistema óseoHistología del sistema óseo
Histología del sistema óseomilenita057
 
Better Full Text Search in PostgreSQL
Better Full Text Search in PostgreSQLBetter Full Text Search in PostgreSQL
Better Full Text Search in PostgreSQLArtur Zakirov
 

Viewers also liked (20)

Concepciones Curriculares
Concepciones CurricularesConcepciones Curriculares
Concepciones Curriculares
 
DiseñOs Curriculares
DiseñOs CurricularesDiseñOs Curriculares
DiseñOs Curriculares
 
D R
D RD R
D R
 
DEKRA Magazine - n. 5 del 2010
DEKRA Magazine - n. 5 del 2010DEKRA Magazine - n. 5 del 2010
DEKRA Magazine - n. 5 del 2010
 
Acetato Mario
Acetato MarioAcetato Mario
Acetato Mario
 
EXPOSICION LAS RSS EN LA EDUCACION
EXPOSICION LAS RSS EN LA EDUCACIONEXPOSICION LAS RSS EN LA EDUCACION
EXPOSICION LAS RSS EN LA EDUCACION
 
DEKRA Magazine - n. 9 del 2011
DEKRA Magazine - n. 9 del 2011DEKRA Magazine - n. 9 del 2011
DEKRA Magazine - n. 9 del 2011
 
PostgreSQL 9.3 and JSON - talk at PgOpen 2013
PostgreSQL 9.3 and JSON - talk at PgOpen 2013PostgreSQL 9.3 and JSON - talk at PgOpen 2013
PostgreSQL 9.3 and JSON - talk at PgOpen 2013
 
Didactica Y Curriculo
Didactica Y CurriculoDidactica Y Curriculo
Didactica Y Curriculo
 
PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...
PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...
PG Day'14 Russia, GIN — Stronger than ever in 9.4 and further, Александр Коро...
 
Pei Autonoma SocializacióN Pra El Vicerrector
Pei Autonoma SocializacióN   Pra El VicerrectorPei Autonoma SocializacióN   Pra El Vicerrector
Pei Autonoma SocializacióN Pra El Vicerrector
 
PostgreSQL Blackhole FDW - lightning talk 2013
PostgreSQL Blackhole FDW - lightning talk 2013PostgreSQL Blackhole FDW - lightning talk 2013
PostgreSQL Blackhole FDW - lightning talk 2013
 
Peru Marketing Symposium 2012 David Rogers
Peru Marketing Symposium 2012 David RogersPeru Marketing Symposium 2012 David Rogers
Peru Marketing Symposium 2012 David Rogers
 
Reinventing Marketing for B2B Services - One North Conference
Reinventing Marketing for B2B Services - One North ConferenceReinventing Marketing for B2B Services - One North Conference
Reinventing Marketing for B2B Services - One North Conference
 
Red Black Tree
Red Black TreeRed Black Tree
Red Black Tree
 
Flexible Indexing with Postgres
Flexible Indexing with PostgresFlexible Indexing with Postgres
Flexible Indexing with Postgres
 
Indexing Complex PostgreSQL Data Types
Indexing Complex PostgreSQL Data TypesIndexing Complex PostgreSQL Data Types
Indexing Complex PostgreSQL Data Types
 
B trees and_b__trees
B trees and_b__treesB trees and_b__trees
B trees and_b__trees
 
Histología del sistema óseo
Histología del sistema óseoHistología del sistema óseo
Histología del sistema óseo
 
Better Full Text Search in PostgreSQL
Better Full Text Search in PostgreSQLBetter Full Text Search in PostgreSQL
Better Full Text Search in PostgreSQL
 

Similar to 9.4json

Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013
Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013
Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013PostgresOpen
 
There is Javascript in my SQL
There is Javascript in my SQLThere is Javascript in my SQL
There is Javascript in my SQLPGConf APAC
 
Postgrtesql as a NoSQL Document Store - The JSON/JSONB data type
Postgrtesql as a NoSQL Document Store - The JSON/JSONB data typePostgrtesql as a NoSQL Document Store - The JSON/JSONB data type
Postgrtesql as a NoSQL Document Store - The JSON/JSONB data typeJumping Bean
 
Tour de Jackson: Forgotten Features of Jackson JSON processor
Tour de Jackson: Forgotten Features of Jackson JSON processorTour de Jackson: Forgotten Features of Jackson JSON processor
Tour de Jackson: Forgotten Features of Jackson JSON processorTatu Saloranta
 
Postgres-XC as a Key Value Store Compared To MongoDB
Postgres-XC as a Key Value Store Compared To MongoDBPostgres-XC as a Key Value Store Compared To MongoDB
Postgres-XC as a Key Value Store Compared To MongoDBMason Sharp
 
Meetup C++ A brief overview of c++17
Meetup C++  A brief overview of c++17Meetup C++  A brief overview of c++17
Meetup C++ A brief overview of c++17Daniel Eriksson
 
javascript objects
javascript objectsjavascript objects
javascript objectsVijay Kalyan
 
Json - ideal for data interchange
Json - ideal for data interchangeJson - ideal for data interchange
Json - ideal for data interchangeChristoph Santschi
 
Updates to the java api for json processing for java ee 8
Updates to the java api for json processing for java ee 8Updates to the java api for json processing for java ee 8
Updates to the java api for json processing for java ee 8Alex Soto
 
Postgres vs Mongo / Олег Бартунов (Postgres Professional)
Postgres vs Mongo / Олег Бартунов (Postgres Professional)Postgres vs Mongo / Олег Бартунов (Postgres Professional)
Postgres vs Mongo / Олег Бартунов (Postgres Professional)Ontico
 
JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...
JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...
JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...Ryan B Harvey, CSDP, CSM
 
JavaScript: Patterns, Part 2
JavaScript: Patterns, Part  2JavaScript: Patterns, Part  2
JavaScript: Patterns, Part 2Chris Farrell
 
Working with JSON Data in PostgreSQL vs. MongoDB
Working with JSON Data in PostgreSQL vs. MongoDBWorking with JSON Data in PostgreSQL vs. MongoDB
Working with JSON Data in PostgreSQL vs. MongoDBScaleGrid.io
 
Json in Postgres - the Roadmap
 Json in Postgres - the Roadmap Json in Postgres - the Roadmap
Json in Postgres - the RoadmapEDB
 

Similar to 9.4json (20)

Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013
Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013
Andrew Dunstan 9.3 JSON Presentation @ Postgres Open 2013
 
There is Javascript in my SQL
There is Javascript in my SQLThere is Javascript in my SQL
There is Javascript in my SQL
 
Postgrtesql as a NoSQL Document Store - The JSON/JSONB data type
Postgrtesql as a NoSQL Document Store - The JSON/JSONB data typePostgrtesql as a NoSQL Document Store - The JSON/JSONB data type
Postgrtesql as a NoSQL Document Store - The JSON/JSONB data type
 
Tour de Jackson: Forgotten Features of Jackson JSON processor
Tour de Jackson: Forgotten Features of Jackson JSON processorTour de Jackson: Forgotten Features of Jackson JSON processor
Tour de Jackson: Forgotten Features of Jackson JSON processor
 
Postgres-XC as a Key Value Store Compared To MongoDB
Postgres-XC as a Key Value Store Compared To MongoDBPostgres-XC as a Key Value Store Compared To MongoDB
Postgres-XC as a Key Value Store Compared To MongoDB
 
An Introduction to Postgresql
An Introduction to PostgresqlAn Introduction to Postgresql
An Introduction to Postgresql
 
Groovy.pptx
Groovy.pptxGroovy.pptx
Groovy.pptx
 
Json the-x-in-ajax1588
Json the-x-in-ajax1588Json the-x-in-ajax1588
Json the-x-in-ajax1588
 
Meetup C++ A brief overview of c++17
Meetup C++  A brief overview of c++17Meetup C++  A brief overview of c++17
Meetup C++ A brief overview of c++17
 
javascript objects
javascript objectsjavascript objects
javascript objects
 
Json - ideal for data interchange
Json - ideal for data interchangeJson - ideal for data interchange
Json - ideal for data interchange
 
Updates to the java api for json processing for java ee 8
Updates to the java api for json processing for java ee 8Updates to the java api for json processing for java ee 8
Updates to the java api for json processing for java ee 8
 
Postgres vs Mongo / Олег Бартунов (Postgres Professional)
Postgres vs Mongo / Олег Бартунов (Postgres Professional)Postgres vs Mongo / Олег Бартунов (Postgres Professional)
Postgres vs Mongo / Олег Бартунов (Postgres Professional)
 
Hello Java 8
Hello Java 8Hello Java 8
Hello Java 8
 
Oh, that ubiquitous JSON !
Oh, that ubiquitous JSON !Oh, that ubiquitous JSON !
Oh, that ubiquitous JSON !
 
JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...
JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...
JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: ...
 
Mathias test
Mathias testMathias test
Mathias test
 
JavaScript: Patterns, Part 2
JavaScript: Patterns, Part  2JavaScript: Patterns, Part  2
JavaScript: Patterns, Part 2
 
Working with JSON Data in PostgreSQL vs. MongoDB
Working with JSON Data in PostgreSQL vs. MongoDBWorking with JSON Data in PostgreSQL vs. MongoDB
Working with JSON Data in PostgreSQL vs. MongoDB
 
Json in Postgres - the Roadmap
 Json in Postgres - the Roadmap Json in Postgres - the Roadmap
Json in Postgres - the Roadmap
 

Recently uploaded

Meet the new FSP 3000 M-Flex800™
Meet the new FSP 3000 M-Flex800™Meet the new FSP 3000 M-Flex800™
Meet the new FSP 3000 M-Flex800™Adtran
 
Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024SkyPlanner
 
Designing A Time bound resource download URL
Designing A Time bound resource download URLDesigning A Time bound resource download URL
Designing A Time bound resource download URLRuncy Oommen
 
AI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just MinutesAI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just MinutesMd Hossain Ali
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfinfogdgmi
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioChristian Posta
 
Babel Compiler - Transforming JavaScript for All Browsers.pptx
Babel Compiler - Transforming JavaScript for All Browsers.pptxBabel Compiler - Transforming JavaScript for All Browsers.pptx
Babel Compiler - Transforming JavaScript for All Browsers.pptxYounusS2
 
Nanopower In Semiconductor Industry.pdf
Nanopower  In Semiconductor Industry.pdfNanopower  In Semiconductor Industry.pdf
Nanopower In Semiconductor Industry.pdfPedro Manuel
 
Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...
Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...
Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...DianaGray10
 
Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.YounusS2
 
UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6DianaGray10
 
Machine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfMachine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfAijun Zhang
 
Cybersecurity Workshop #1.pptx
Cybersecurity Workshop #1.pptxCybersecurity Workshop #1.pptx
Cybersecurity Workshop #1.pptxGDSC PJATK
 
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019IES VE
 
Secure your environment with UiPath and CyberArk technologies - Session 1
Secure your environment with UiPath and CyberArk technologies - Session 1Secure your environment with UiPath and CyberArk technologies - Session 1
Secure your environment with UiPath and CyberArk technologies - Session 1DianaGray10
 
Introduction to Matsuo Laboratory (ENG).pptx
Introduction to Matsuo Laboratory (ENG).pptxIntroduction to Matsuo Laboratory (ENG).pptx
Introduction to Matsuo Laboratory (ENG).pptxMatsuo Lab
 
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdfIaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdfDaniel Santiago Silva Capera
 
GenAI and AI GCC State of AI_Object Automation Inc
GenAI and AI GCC State of AI_Object Automation IncGenAI and AI GCC State of AI_Object Automation Inc
GenAI and AI GCC State of AI_Object Automation IncObject Automation
 
OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureEric D. Schabell
 
UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7DianaGray10
 

Recently uploaded (20)

Meet the new FSP 3000 M-Flex800™
Meet the new FSP 3000 M-Flex800™Meet the new FSP 3000 M-Flex800™
Meet the new FSP 3000 M-Flex800™
 
Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024
 
Designing A Time bound resource download URL
Designing A Time bound resource download URLDesigning A Time bound resource download URL
Designing A Time bound resource download URL
 
AI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just MinutesAI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just Minutes
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdf
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and Istio
 
Babel Compiler - Transforming JavaScript for All Browsers.pptx
Babel Compiler - Transforming JavaScript for All Browsers.pptxBabel Compiler - Transforming JavaScript for All Browsers.pptx
Babel Compiler - Transforming JavaScript for All Browsers.pptx
 
Nanopower In Semiconductor Industry.pdf
Nanopower  In Semiconductor Industry.pdfNanopower  In Semiconductor Industry.pdf
Nanopower In Semiconductor Industry.pdf
 
Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...
Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...
Connector Corner: Extending LLM automation use cases with UiPath GenAI connec...
 
Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.
 
UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6
 
Machine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfMachine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdf
 
Cybersecurity Workshop #1.pptx
Cybersecurity Workshop #1.pptxCybersecurity Workshop #1.pptx
Cybersecurity Workshop #1.pptx
 
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
 
Secure your environment with UiPath and CyberArk technologies - Session 1
Secure your environment with UiPath and CyberArk technologies - Session 1Secure your environment with UiPath and CyberArk technologies - Session 1
Secure your environment with UiPath and CyberArk technologies - Session 1
 
Introduction to Matsuo Laboratory (ENG).pptx
Introduction to Matsuo Laboratory (ENG).pptxIntroduction to Matsuo Laboratory (ENG).pptx
Introduction to Matsuo Laboratory (ENG).pptx
 
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdfIaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
 
GenAI and AI GCC State of AI_Object Automation Inc
GenAI and AI GCC State of AI_Object Automation IncGenAI and AI GCC State of AI_Object Automation Inc
GenAI and AI GCC State of AI_Object Automation Inc
 
OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability Adventure
 
UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7
 

9.4json

  • 1. PostgreSQL 9.4 and JSON Andrew Dunstan andrew@dunslane.net andrew.dunstan@pgexperts.com
  • 2. Overview ● What is JSON? Why use JSON? ● Quick review of 9.2, 9.3 features ● 9.4 new features ● Future work
  • 3. What is JSON? ● Data serialization format – rfc 7158, previously rfc 4627 ● Lightweight ● Human readable ● Becoming ubiquitous ● Simpler and more compact than XML
  • 4. What it looks like { "books" : [ { "title": "Catch 22”, "author": "Joseph Heller"}, { "title": "Catcher in the Rye", "author": "J. D. Salinger"} ], "publishers": [ { "name": "Random House" }, { "name": "Penguin" } ], "active": true, "version": 35, "date": "2003-09-13", “reference”: null } Scalars: ● quoted strings ● numbers ● true, false, null No extensions No date/time types
  • 5. Why use it? ● Everyone is moving that way ● Understood everywhere there is a JavaScript interpreter – Especially browsers ● ... and in a large number of other languages – e.g. Perl, Python ● node.js is becoming very widely used ● More compact than XML ● Most applications don't need the richer structure of XML
  • 6. Why not use it? ● Overly verbose ● Field names are repeated ● Arguably less readable than, say, YAML ● Not suitable for huge objects ● Not quite type rich enough – No timestamp support
  • 7. Review – pre 9.2 facilities Nothing – store JSON as text ● No validation ● No JSON production ● No JSON extraction
  • 8. Review – 9.2 data type New JSON type ● Stored as text ● Reasonably performant state-based validating parser ● Kudos: Robert Haas
  • 9. Review – 9.2 production functions ● Turn non-JSON data into JSON ● row_to_json(anyrecord) ● array_to_json(anyarray) ● Optional second param for pretty printing ● My humble contribution ☺
  • 10. What's missing? ● JSON production features are incomplete ● JSON processing is totally absent ● Have to use PLV8, PLPerl or some such
  • 11. 9.3 Features – JSON production ● to_json(any) ● Can be used on any datum, not just arrays and records ● json_agg(record) ● Much faster than array_to_json(array_agg(record))
  • 12. 9.3 and casts to JSON ● Production functions honor casts to JSON for non-builtin types ● Not needed for builtins, as we know how to convert them ● Saves syscache lookups where we know it's not necessary ● Is this wise, or necessary? – Counter case is ISO 8601 Timestamps – Workaround – use to_char()
  • 13. 9.3 hstore and JSON ● hstore_to_json(hstore) ● Also used as a cast function ● hstore_to_json_loose(hstore) ● Uses heuristics about whether or not certain possibly numeric and boolean values need to be quoted.
  • 14. 9.3 JSON parser rewrite ● New parser uses recursive descent pattern ● Caller can supply event handlers for certain events ● c.f. XML SAX parsers ● Validator uses NULL handlers for all events ● Tokenizing routines of previous parser largely kept
  • 15. 9.3 JSON processing functions ● All leverage new parser API ● Operators give a more natural style to extraction operations ● Many have two forms, producing either JSON output, which can be further processed, or text output, which cannot. ● Text output is de-escaped and dequoted
  • 16. 9.3 extraction operators (1) ● -> fetch an array element or object member as json ● '[4,5,6]'::json->2 ⟹ 6 – json arrays are 0 based, unlike SQL arrays ● '{"a":1,"b":2}'::json->'b' ⟹ 2
  • 17. 9.3 extraction operators (2) ● ->> fetch an array element orobject member as text ● '["a","b","c"]'::json->2 ⟹ c – Instead of "c"
  • 18. 9.3 extraction operators (3) ● #> and #>> fetch data pointed at by a path ● Path is an array of text elements ● Treats arrays correctly by some trying to treat path element as an integer of necessary ● '{"a":[6,7,8]}'::json#>'{a,1}' ⟹ 7 ●
  • 19. 9.3 extraction functions ● json_extract_path(json, VARIADIC path_elems text[]); ● json_extract_path_text(json, VARIADIC path_elems text[]); ● Same as #> and #>> operators, but you can pass the path as a variadic array ● json_extract_path('{"a":[6,7,8]}','a','1') ⟹ 7
  • 20. 9.3 turn JSON into records ● CREATE TYPE x AS (a int, b int); ● SELECT * FROM json_populate_record(null::x, '{"a":1,"b":2}', false); ● SELECT * FROM json_populate_recordset(null::x,'[{"a":1," b":2},{"a":3,"b":4}]', false);
  • 21. 9.3 turn JSON into key/value pairs ● SELECT * FROM json_each('{"a":1,"b":"foo"}') ● SELECT * FROM json_each_text('{"a":1,"b":"foo"}') ● Deliver columns named “key” and “value”
  • 22. 9.3 get keys from JSON object ● SELECT * FROM json_object_keys('{"a":1,"b":"foo"}')
  • 23. 9.3 JSON array processing ● SELECT json_array_length('[1,2,3,4]'); ● SELECT * FROM json_array_elements('[1,2,3,4]')
  • 24. 9.3 API extension example ● Code can be cloned from https://bitbucket.org/adunstan/json_typeof ● See also jsonfuncs.c for lots of examples of use.
  • 25. What's missing in 9.3? ● Efficiency ● Richer querying ● Canonicalization ● Indexing ● Complete Utilities for building json ● CRUD operations
  • 26. 9.4 JSON features ● New json creation functions ● New utility functions ● jsonb type – Efficient operations – Indexable – Canonical
  • 27. 9.4 Features – new json aggregate ● json_object_agg(“any”, “any”) ● Turn a set of key value pairs int a json object ● Select json_object_agg(name, population) from cities; ● { “Smallville”: 300, “Metropolis”: 1000000}
  • 28. 9.4 Features – json creation functions ● json_build_object( VARIADIC “any”) ● json_build_array(VARIADIC “any”) ● json_object(text[]) ● json_object(keys text[], values text[])
  • 29. 9.4 json creation simple examples ● select json_build_object('a',1,'b',true) ● {“a”: 1, “b”: true} ● select json_build_array('a',1,'b',true) ● [“a”, 1, “b”, true] ● select json_object(array['a','b','c','d'] ● Or select json_object(array[['a','b'],['c','d']] ● Or select json_object(array['a','c'],array['b','d']) ● {“a”:”b”, “c”:”d”}
  • 30. 9.4 json creation complex example select json_build_object( 'a', json_build_array('b',false,'c',99), 'd', json_build_object('e',array[9,8,7]::int[], 'f', (select to_json(r) from ( select relkind, oid::regclass as name from pg_class where relname = 'pg_class') r)), 'g', json_object(array[['w','x'],['y','z']])); {"a" : ["b", false, "c", 99], "d" : {"e" : [9,8,7], "f" : {"relkind":"r","name ":"pg_class"}}, "g" : {"w" : "x", "y" : "z"}}
  • 31. 9.4 features – json_typeof ● json_typeof(json) returns text – Result is one of: ● 'object' ● 'array' ● 'string' ● 'number' ● 'boolean' ● 'null' ● Null ● Kudos: Andrew Tipton
  • 32. 9.4 features – jsonb type ● Accepts the same inputs as json – Uses the 9.3 parsing API – Checks Unicode escapes, especially use of surrogate pairs, more thoroughly than json. ● Representation closely mirrors json syntax
  • 33. 9.4 Features jsonb kudos ● Originally grew out of work on nested hstore – Major kudos to Oleg Bartunov, Teodor Sigaev, Alexander Korotkov – Adaptation of indexable operators by Peter Geoghegan – Most of parser, and implementation of json functions and operators for jsonb by moi
  • 34. 9.4 Features – jsonb canonical representation ● Whitespace and punctuation dissolved away ● Only one value per object key is kept – Last one wins. – Key order determined by length, then bytewise comparison
  • 35. 9.4 Features – jsonb operators ● Has the json operators with the same semantics: -> ->> #> #>> ● Has standard equality and inequality operators = <> > < >= <= ● Has new operations testing containment, key/element presence @> <@ ? ?| ?&
  • 36. 9.4 Features – jsonb equality and inequality ● Comparison is piecewise – Object > Array > Boolean > Number > String > Null – Object with n pairs > object with n - 1 pairs – Array with n elements > array with n - 1 elements ● Not particularly intuitive ● Not ECMA standard ordering, which is possibly not suitable anyway
  • 37. 9.4 features – jsonb functions ● jsonb has all the json processing functions, with the same semantics – i.e. functions that take json arguments – Function names start with jsonb_ instead of json_ ● jsonb does not have any of the json creation functions – i.e. functions that take non-json arguments and output json – Workaround: cast result to jsonb
  • 38. 9.4 features – jsonb indexing ● For more details see Oleg, Teodor and Alexander's Vodka talk from yesterday. ● 2 ops classes for GIN indexes – Default supports contains and exists operators: @> ? ?& ?| – Non-default ops class jsonb_path_ops only supports @> operator – Faster – Smaller indexes
  • 39. 9.4 features – jsonb subdocument indexes ● Use “get” operators to construct expression indexes on subdocument: – CREATE INDEX author_index ON books USING GIN ((jsondata -> 'authors')); – SELECT * FROM books WHERE jsondata -> 'authors' ? 'Carl Bernstein';
  • 40. When to use json, when jsonb ● If you need any of these, use json – Storage of validated json, without processing or indexing it – Preservation of white space in json text – Preservation of object key order – Preservation of duplicate object keys – Maximum input/output speed ● For any other case, use jsonb
  • 41. Future of JSON in PostgreSQL ● More indexing options – Vodka! – Further requirements will emerge from use ● Json alteration operations – e.g. Set a field, or delete an element ● General document store – Can we get around the “rewrite a whole datum” issue
  • 42. Unconference issues ● Statistics? ● Planner support? ● ???