PostgreSQL 9.4 – How to Push or Append to JSON Array

arrayjsonpostgresqlpostgresql-9.4

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values.

Something like that:

CREATE OR REPLACE FUNCTION add_array(
    array_received json[])
  RETURNS void AS
$BODY$

    update table set _array_field = _array_field | array_received ...;

$BODY$
  LANGUAGE plpgsql VOLATILE;

Best Answer

In pre-9.5 you can use json_array_elements, and array_to_json(array_agg()), like this.

SELECT array_to_json(array_agg(x))
FROM (
  SELECT jsonb_array_elements('[1,2,3]'::jsonb)
  UNION ALL SELECT '4'::jsonb
) AS t(x);

You can also write this using the ARRAY constructor like this..

SELECT array_to_json(ARRAY(
  SELECT jsonb_array_elements('[1,2,3]'::jsonb)
  UNION ALL SELECT '4'::jsonb
));

Why?

The expressed requirement that artist and title must match in the same element of the JSON array is not reflected in your query, which finds all rows where at least one element matches the artist and another (possibly a different one) matches the title.

The example data for your first case was inconclusive, since the query cannot fail this way for a single array element. Your second example demonstrates the case well, though.

Solution in Postgres 9.3

There are various ways to fix this. One way would be to translate each json array element to an SQL array of composite type consisting of artist and title and match the whole type as one.

Another way would be to keep indexes like you have now (or even a single composite spanning both expressions: (json2arr(data, 'artist'), json2arr(data, 'title')). Your current query identifies possible matches. Unnest the json array data for all identified candidates and check whether both artist and title match on the same element. May or may not be efficient enough.

Better use `jsonb` in n Postgres 9.4

I am not going into detail for json / pg 9.3, because the release of jsonb in Postgres 9.4 with advanced indexing capabilities mostly obsoleted the problem. This can be implemented much simpler and more efficient with a native GIN index on a jsonb column. There are various options. To optimize for the presented case:

CREATE TABLE tracks (id serial, data jsonb);

INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect"}]');

Index:

CREATE INDEX tracks_data_gin_idx ON tracks USING gin (data jsonb_path_ops);

Query:

SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]';

Sequence of attributes in the JSON value and insignificant whitespace don't matter for jsonb. I added detailed information for jsonb to the referenced answer on SO:

Index for finding an element in a JSON array

PostgreSQL – Use Trigger to Synchronize JSON Columns on Insert or Update

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:

Assign to NEW by key in a Postgres trigger

Both solution can be derived from my answer that Daniel already referenced:

How to set value of composite variable field using dynamic SQL

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

Related Solutions

PostgreSQL – Indexing for Attribute Combinations in JSON Array