Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE "values" (
    pk TEXT,
    i INTEGER,
    value REAL,
    PRIMARY KEY (pk, i)
);
insert into "values"
values
('A', 0, 17.5),
('A', 1, 32.7),
('A', 3, 5.3),
('B', 5, 13.5),
('B', 2, 4.8),
('B', 4, 89.1);
6 rows affected

CREATE FUNCTION array_set(arr anyarray, index int, val anyelement) RETURNS anyarray
AS $$
BEGIN
    arr[index] = val;
    RETURN arr;
END
$$ LANGUAGE plpgsql STRICT;
CREATE FUNCTION array_fillup(arr anyarray) RETURNS anyarray
AS $$
BEGIN
   IF array_lower(arr, 1) > 1 THEN
       arr[1] = NULL;
   END IF;
   RETURN arr;
END
$$ LANGUAGE plpgsql STRICT;

CREATE AGGREGATE array_by_subscript(int, anyelement) (
 sfunc = array_set,
 stype = anyarray,
 initcond = '{}',
 finalfunc = array_fillup
);
SELECT
 fk,
 to_json(array_by_subscript(i+1, value)),
 array_by_subscript(i+1, value)
FROM unnest(ARRAY['A', 'C', 'B']) as n(fk)
LEFT JOIN "values" ON (fk=pk)
GROUP BY fk;


fk
to_json
array_by_subscript


B
[null,null,4.8,null,89.1,13.5]
{NULL,NULL,4.8,NULL,89.1,13.5}

C
[]
{}

A
[17.5,32.7,null,5.3]
{17.5,32.7,NULL,5.3}

fk	to_json	array_by_subscript
B	[null,null,4.8,null,89.1,13.5]	{NULL,NULL,4.8,NULL,89.1,13.5}
C	[]	{}
A	[17.5,32.7,null,5.3]	{17.5,32.7,NULL,5.3}