PostgreSQL – Inserting a Row Represented by JSON into a Table

functionsinsertjsonpostgresqlrow

I've been laboring away trying to create a procedure which can data JSONB (or JSON, doesn't really matter), a table, and convert the JSON to a record and insert that record into the table. This is (roughly) the closest I've come to getting a functional prototype:

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, table_type anyelement) RETURNS VOID
    SECURITY INVOKER
    LANGUAGE plpgsql
AS $$
    DECLARE
        record RECORD;
        record_type RECORD;
    BEGIN
        EXECUTE format('SELECT x.* FROM (SELECT a.* FROM CAST(NULL as %s) a) x', pg_typeof(table_type))
            INTO record_type;
        record := jsonb_populate_record(record_type, data);
        INSERT INTO users SELECT (record).* RETURNING id;
    END;
$$;

So let's say that I do the following:

SELECT fn_alter_entity('{"a": "b"}'::JSONB, users);

I receive the following error:

ERROR: record type has not been registered
SQL state: 42809
Context: SQL statement "INSERT INTO users SELECT (record).* RETURNING id"
PL/pgSQL function fn_alter_entity(jsonb,anyelement) line 9 at SQL statement

Now, obviously the reason I'm receiving that error is that record_type is not actually a real record type. Now, normally we would just do something like jsonb_populate_record(data, NULL::users) or jsonb_populate_record(data, NULL::events) or the like. But what if I want that record type to be dynamic? Is there any possibility? I feel like I'm on the verge of nailing this down, but just can't quite get over the hump. All I want to do is be able to transform JSON into a record.

Best Answer

I think you want something like this.

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, t TEXT )
  RETURNS VOID
  SECURITY INVOKER
AS $$
    BEGIN
        EXECUTE FORMAT(
          'INSERT INTO %I SELECT * FROM jsonb_populate_record( null::%I, $1);',
          t,
          t
        )
        USING data;
    END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TABLE foo ( a int );
CREATE TABLE bar ( a int, b text );

SELECT fn_alter_entity( '{"a": 5}', 'foo' );
SELECT fn_alter_entity( '{"a": 42}', 'foo' );
SELECT fn_alter_entity( '{"a": 5, "b": "foo"}', 'bar' );
SELECT fn_alter_entity( '{"a": 42, "b": "baz"}', 'bar' );

test=# TABLE foo;
 a  
----
  5
 42
(2 rows)

test=# TABLE bar;
 a  |  b  
----+-----
  5 | foo
 42 | baz
(2 rows)

But seeing where you may be going, I would highly suggest you look into PostgREST.

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Postgresql – CRUD operations on json data

Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb is your data type.

9.3 json https://gist.github.com/matheusoliveira/9488951

(and while jsonb is not an option for Michael, for those who are using 9.4, there is a jsonb variant: https://gist.github.com/inindev/2219dff96851928c2282)

Operations Provided:

postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
        json_append        
---------------------------
 {"a":1,"b":2,"c":3,"a":4}
(1 row)

postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
 json_delete 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
 json_update 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
     json_merge      
---------------------
 {"b":2,"c":3,"a":4}
(1 row)

Best Answer

Related Solutions

PostgreSQL – Is There a Type-Safe first() Aggregate Function?

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Postgresql – CRUD operations on json data

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`