PostgreSQL – How to Prepend an Element to a JSON Array?

arrayjsonplpgsqlpostgresqlpostgresql-9.6

I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty).

How can I prepend the value of _x to the array _y?

I was trying to do it like this:

SELECT jsonb_build_array(_x, VARIADIC array_agg(value))
FROM jsonb_array_elements_text(_y)
INTO _y;

which is wrong. I was trying to use info about VARIADIC from this answer.

Best Answer

Burns down to a simple solution:

_y := to_jsonb(ARRAY[_x]) || _y;

This is concatenating two jsonb arrays with the || operator.. You need to wrap the simple text value _x into a jsonb array for this. There are various way, to_jsonb(ARRAY[_x]) is one.

Or even just:

_y := to_jsonb(_x) || _y;

The manual:

The || operator concatenates the elements at the top level of each of its operands. [...]

So this happens to work for element-to-array concatenation as well.

You could unnest the array and reconstruct it (like you were trying), but that's more expensive. And might be tricky since a JSON array can hold elements of various types, unlike a Postgres array. (Which would not be a problem for your JSONB array of text.)

Your attempt failed because (as commented at the related answer), jsonb_build_array() expects a list of values, which can be replaced with a single array following the keyword VARIADIC - but not with a single element, followed by VARIADIC array.

Related Solutions

PostgreSQL RETURN NEXT Function – How to Use

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
  c refcursor;
BEGIN
  c:='mycursorname';
  OPEN c FOR select * from generate_series(1,100000);
  return c;                                       
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;

 Output:

 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.

PostgreSQL function param json_populate_recordset ARRAY problem

Confusion?

From the docs json_populate_recordset(base anyelement, from_json json) does not return an ARRAY, it returns a setof anyelement. That's fundamentally different from an array.

SELECT * FROM (SELECT ARRAY[1,2]) AS gs(x);   -- array (int[])
SELECT * FROM (VALUES (1), (2)  ) AS gs(x);   -- setof anyelement (setof int)

I am of the opinion that you do not need an array, nor a setof anyelement.

What you need to do things your way

For comparison this will turn json into foo[],

CREATE TYPE foo AS ( id int );
SELECT pg_typeof(
  ARRAY(
    SELECT *
    FROM json_populate_recordset( NULL::foo, '[{"id":1},{"id":2}]' ) )
  )
);

Which you can call like this

SELECT test_gps(
  ARRAY(
    SELECT *
    FROM json_populate_recordset( NULL::foo, '[{"id":1},{"id":2}]' ) )
  )
);

A better way!

But the better way is to simply not process the whole result set but only a single json value.

CREATE OR REPLACE FUNCTION test_gps
  (
    gps_points API_GPS_POINT
  )
  RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
  api_gps_points API_GPS_POINT;
BEGIN

    RAISE NOTICE 'API_GPS_POINT : %', API_GPS_POINT;

END;
$$;

And then call it like this..

SELECT test_gps(myjson)
FROM json_populate_recordset( null::foo, '[{"id":1},{"id":2}]' )
  AS myjson;

Which you can see returns one type of foo.

SELECT pg_typeof(myjson)
FROM json_populate_recordset( null::foo, '[{"id":1},{"id":2}]' )
  AS myjson;