More functions for json/yaml/xml plans on explain.depesz.com

Added two new functions, which basically work only for JSON / YAML / XML plans.

First – the view of source explain is now syntax highlighted. Just click source button on one of these to see:

Next additon is text tab, which shows the same explain but converted to text-format.

Hope you'll find it useful.

Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language

On 16th of March 2019, Alexander Korotkov committed patch:

Partial implementation of SQL/JSON path language
 
SQL 2016 standards among other things contains set of SQL/JSON features for
JSON processing inside of relational database.  The core of SQL/JSON is JSON
path language, allowing access parts of JSON documents and make computations
over them.  This commit implements partial support JSON path language as
separate datatype called "jsonpath".  The implementation is partial because
it's lacking datetime support and suppression of numeric errors.  Missing
features will be added later by separate commits.
 
Support of SQL/JSON features requires implementation of separate nodes, and it
will be considered in subsequent patches.  This commit includes following
set of plain functions, allowing to execute jsonpath over jsonb values:
 
 * jsonb_path_exists(jsonb, jsonpath[, jsonb, bool]),
 * jsonb_path_match(jsonb, jsonpath[, jsonb, bool]),
 * jsonb_path_query(jsonb, jsonpath[, jsonb, bool]),
 * jsonb_path_query_array(jsonb, jsonpath[, jsonb, bool]).
 * jsonb_path_query_first(jsonb, jsonpath[, jsonb, bool]).
 
This commit also implements "jsonb @? jsonpath" and "jsonb @@ jsonpath", which
are wrappers over jsonpath_exists(jsonb, jsonpath) and jsonpath_predicate(jsonb,
jsonpath) correspondingly.  These operators will have an index support
(implemented in subsequent patches).
 
Catversion bumped, to add new functions and operators.
 
Code was written by Nikita Glukhov and Teodor Sigaev, revised by me.
Documentation was written by Oleg Bartunov and Liudmila Mantrova.  The work
was inspired by Oleg Bartunov.
 
Discussion: https://postgr.es/m/-b497-f39a-923d-%402ndQuadrant.com
Author: Nikita Glukhov, Teodor Sigaev, Alexander Korotkov, Oleg Bartunov, Liudmila Mantrova

Continue reading Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language

Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function

On 7th of April 2018, Teodor Sigaev committed patch:

Add json(b)_to_tsvector function
 
Jsonb has a complex nature so there isn't best-for-everything way to convert it
to tsvector for full text search. Current to_tsvector(json(b)) suggests to
convert only string values, but it's possible to index keys, numerics and even
booleans value. To solve that json(b)_to_tsvector has a second required
argument contained a list of desired types of json fields. Second argument is
a jsonb scalar or array right now with possibility to add new options in a
future.
 
Bump catalog version
 
Author: Dmitry Dolgov with some editorization by me
Reviewed by: Teodor Sigaev
Discussion: https://www.postgresql.org/message-id/CA+q6zcXJQbS1b4kJ_HeAOoOc=unfnOrUEL=KGgE32QKDww7d8g@mail.gmail.com

Continue reading Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function

Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb

On 31st of March 2017, Andrew Dunstan committed patch:

Full Text Search support for json and jsonb
 
The new functions are ts_headline() and to_tsvector.
 
Dmitry Dolgov, edited and documented by me.

Continue reading Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb

Waiting for 9.5 – Rename jsonb_replace to jsonb_set and allow it to add new values

On 1st of June, Andrew Dunstan committed patch:

Rename jsonb_replace to jsonb_set and allow it to add new values
 
The function is given a fourth parameter, which defaults to true. When
this parameter is true, if the last element of the path is missing
in the original json, jsonb_set creates it in the result and assigns it
the new value. If it is false then the function does nothing unless all
elements of the path are present, including the last.
 
Based on some original code from Dmitry Dolgov, heavily modified by me.
 
Catalog version bumped.

Continue reading Waiting for 9.5 – Rename jsonb_replace to jsonb_set and allow it to add new values

Waiting for 9.5 – Additional functions and operators for jsonb

On 12th of May, Andrew Dunstan committed patch:

Additional functions and operators for jsonb
 
jsonb_pretty(jsonb) produces nicely indented json output.
jsonb || jsonb concatenates two jsonb values.
jsonb - text removes a key and its associated value from the json
jsonb - int removes the designated array element
jsonb - text[] removes a key and associated value or array element at
the designated path
jsonb_replace(jsonb,text[],jsonb) replaces the array element designated
by the path or the value associated with the key designated by the path
with the given value.
 
Original work by Dmitry Dolgov, adapted and reworked for PostgreSQL core
by Andrew Dunstan, reviewed and tidied up by Petr Jelinek.

Continue reading Waiting for 9.5 – Additional functions and operators for jsonb

Waiting for 9.4 – Introduce jsonb, a structured format for storing json.

Portuguese Brazil Version

On 23rd of March, Andrew Dunstan committed patch:

Introduce jsonb, a structured format for storing json.
 
The new format accepts exactly the same data as the json type. However, it is
stored in a format that does not require reparsing the orgiginal text in order
to process it, making it much more suitable for indexing and other operations.
Insignificant whitespace is discarded, and the order of object keys is not
preserved. Neither are duplicate object keys kept - the later value for a given
key is the only one stored.
 
The new type has all the functions and operators that the json type has,
with the exception of the json generation functions (to_json, json_agg etc.)
and with identical semantics. In addition, there are operator classes for
hash and btree indexing, and two classes for GIN indexing, that have no
equivalent in the json type.
 
This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, which
was intended to provide similar facilities to a nested hstore type, but which
in the end proved to have some significant compatibility issues.
 
Authors: Oleg Bartunov,  Teodor Sigaev, Peter Geoghegan and Andrew Dunstan.
Review: Andres Freund

Continue reading Waiting for 9.4 – Introduce jsonb, a structured format for storing json.

Waiting for 9.4 – New json functions.

On 28th of January, Andrew Dunstan committed patch:

New json functions.
 
json_build_array() and json_build_object allow for the construction of
arbitrarily complex json trees. json_object() turns a one or two
dimensional array, or two separate arrays, into a json_object of
name/value pairs, similarly to the hstore() function.
json_object_agg() aggregates its two arguments into a single json object
as name value pairs.
 
Catalog version bumped.
 
Andrew Dunstan, reviewed by Marko Tiikkaja.

Continue reading Waiting for 9.4 – New json functions.

Waiting for 9.3 – Add new JSON processing functions and parser API.

On 29th of March, Andrew Dunstan committed patch:

Add new JSON processing functions and parser API.
 
The JSON parser is converted into a recursive descent parser, and
exposed for use by other modules such as extensions. The API provides
hooks for all the significant parser event such as the beginning and end
of objects and arrays, and providing functions to handle these hooks
allows for fairly simple construction of a wide variety of JSON
processing functions. A set of new basic processing functions and
operators is also added, which use this API, including operations to
extract array elements, object fields, get the length of arrays and the
set of keys of a field, deconstruct an object into a set of key/value
pairs, and create records from JSON objects and arrays of objects.
 
Catalog version bumped.
 
Andrew Dunstan, with some documentation assistance from Merlin Moncure.

Continue reading Waiting for 9.3 – Add new JSON processing functions and parser API.