How to convert array with non-standard indexes to json?

Question

I need to convert a text[] array not starting from index 1 to json, preserving its indexes.

For example, I have text[] array which starts from index 5:

select myarr from tbl;

myarr       
-------------------
 [5:5]={blablabla}

If I use array_to_json() function I get:

select array_to_json(myarr) from tbl;

 array_to_json 
---------------
 ["blablabla"]

Information about array index has been lost. But I need it.

I need to get it in correct view (for json) which is:

[null, null, null, null, "blablabla"]

PS, I wrapped the given solution in a function:

CREATE OR REPLACE FUNCTION array_to_json_with_null(myarr text[])
  RETURNS json AS
$func$
BEGIN
   if myarr is null or cardinality(myarr) = 0 then
    return null;
   else
    return array_to_json (array_fill(null::text, 
ARRAY[array_lower(myarr, 1) - 1], ARRAY[1]) || myarr);
   end if;
END
$func$  LANGUAGE plpgsql;

Erwin Brandstetter · Answer 1 · 2019-04-19T16:09:33.857

You can extend the Postgres array with NULL values before the conversion. The function array_fill() comes in handy for this.

Assuming 1-dimenstional arrays with positive array indexes. And data type text[] for the array:

WITH tbl(arr) AS (SELECT '[5:5]={blablabla}'::text[])
SELECT array_to_json(
            array_fill(NULL::text, ARRAY[array_lower(arr, 1) - 1], ARRAY[1])
         || arr)
FROM   tbl;

Building on the plpgsql function you added to the question. I suggest a simple SQL function:

CREATE OR REPLACE FUNCTION array_to_json_with_null_padding(myarr text[])
  RETURNS json AS
$func$
SELECT CASE WHEN cardinality(myarr) > 0
            THEN array_to_json(array_fill(null::text
                                        , ARRAY[array_lower(myarr, 1) - 1]
                                        , '{1}')
                            || myarr)
       END
$func$  LANGUAGE sql STABLE;

Inverting the logic allows a simpler test and no ELSE clause, which defaults to NULL automatically.

Volatility STABLE, because array_to_json() is only STABLE and not IMMUTABLE. This allows function inlining - which is also why my function is not STRICT). But cannot be used as index expression, which would require IMMUTABLE.

How to convert array with non-standard indexes to json?

1 Answers1

Function wrapper