If you are on 9.4 something like this might be what you are after:
select json_object(array_agg(id)::text[],array_agg(rw)::text[])
from( select id
, ( select to_json(array_agg(row_to_json(t)))
from (select typ,prop from bgb where id=b.id) t ) rw
from bgb b
group by id ) z;
Actually, this is all you need:
NEW := jsonb_populate_record(NEW, NEW.json);
Per documentation:
jsonb_populate_record(base anyelement, from_json jsonb)
Expands the object in from_json
to a row whose columns match the
record type defined by base (see note below).
What's not documented: The row provided as first argument retains all values that are not overwritten (no matching key in the json value). I see no reason why this should change, but you cannot fully rely on it unless it's documented.
One thing to note - you wrote:
Setting the column to NULL if the corresponding json field does not
exist is fine.
This retains all values with no matching key in the JSON value, which should be even better.
If "undocumented" is too uncertain for you, use the hstore
operator #=
doing exactly the same.
NEW := (NEW #= hstore(jsonb_populate_record(NEW, NEW.json)));
The hstore
module should be installed in most systems anyway. Instructions:
Both solution can be derived from my answer that Daniel already referenced:
Function code
CREATE OR REPLACE FUNCTION json_fn()
RETURNS TRIGGER AS
$func$
BEGIN
NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Everything else in your setup looks right, just add a PK to testy
:
CREATE TABLE testy (
id int PRIMARY KEY REFERENCES testy_index
, data jsonb NOT NULL
);
Tested in pg 9.4 and it works for me as advertised. I doubt that the PLv8 function can rival performance and simplicity.
Set other columns to NULL
As per comment:
CREATE OR REPLACE FUNCTION json_fn()
RETURNS TRIGGER AS
$func$
DECLARE
_j jsonb := NEW.json; -- remember the json value
BEGIN
NEW := jsonb_populate_record(NULL::testy, _j);
NEW.json := _j; -- reassign
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Obviously, you need to make sure that the column name or your jsonb
column does not appear as key name in the JSON value. And i wouldn't use json
as column name, since it's a data type name and that can get confusing.
Best Answer
Well, it would have been a lot faster and easier with 9.4, but it's doable with 9.3 in simple situations.
Not fun, but thankfully this is a one-time thing.
This can't be used to update a table, but you can create a temporary table and insert the values there first.