This question was asked at least twice on some support channel. Getting a row is trivial: select * from table where id = ?. But what about dependencies – the rows that this exported row references?
Decided to take a look at this task.
Continue reading How to get a row, and all of it's dependencies?
On 4th of April 2022, Andrew Dunstan committed patch:
This feature allows jsonb data to be treated as a table and thus used in
a FROM clause like other tabular data. Data can be selected from the
jsonb using jsonpath expressions, and hoisted out of nested structures
in the jsonb to form multiple rows, more or less like an outer join.
Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu (whose
name I previously misspelled), Himanshu Upadhyaya, Daniel Gustafsson,
Continue reading Waiting for PostgreSQL 15 – JSON_TABLE
Every so often, on irc, someone asks how to get value from column that is passed as argument.
This is generally seen as not possible, as pl/PgSQL doesn't have support for dynamic column names.
We can work around it, though. Are the workarounds usable, in terms of performance?
Continue reading Getting value from dynamic column in pl/PgSQL triggers?
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
Bump catalog version
Author: Dmitry Dolgov with some editorization by me
Reviewed by: Teodor Sigaev
Continue reading Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function
On 28th of March 2018, Peter Eisentraut committed patch:
Transforms for jsonb to PL/Python
Add a new contrib module jsonb_plpython that provide a transform between
jsonb and PL/Python. jsonb values are converted to appropriate Python
types such as dicts and lists, and vice versa.
Author: Anthony Bykov <firstname.lastname@example.org>
and then, on 3rd of April 2018, he also committed patch:
Transforms for jsonb to PL/Perl
Add a new contrib module jsonb_plperl that provides a transform between
jsonb and PL/Perl. jsonb values are converted to appropriate Perl types
such as arrays and hashes, and vice versa.
Author: Anthony Bykov <email@example.com>
Continue reading Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl
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
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
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
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.