"ERROR:malformed array literal" when using json_to_record with a JSON array element in Postgres 9.4

Question

This illustrates the issue nicely:

When column b is of type text, and not an array, the following works:

select * 
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') 
    as x(a int, b text, d text);

 a |         b          | d
---+--------------------+---
 1 | ["hello", "There"] |

But if I define the b column as an array, I get this error:

select * 
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') 
    as x(a int, b text[], d text)

ERROR:  malformed array literal: "["hello", "There"]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.

How can I convince/coerce json_to_record (or json_populate_record) to convert a JSON array into the Postgres array of the target column type?

Answer 1

Just a slight variation to Chris's answer:

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM json_to_record('{"a": 1, "b": ["hello", "There"], "c": "bar"}')
AS x(a int, b text, d text);

The idea is the same: massage the JSON array into an array - in this case, through an array literal. In addition to a bit cleaner looking code (though I love it, regex usually does not help much in this regard :), it seems slighly faster, too:

CREATE TABLE jsonb_test (
    id serial,
    data jsonb
);

INSERT INTO jsonb_test (id, data)
SELECT i, format('{"a": %s, "b": ["foo", "bar"], "c": "baz"}', i::text)::jsonb 
FROM generate_series(1,10000) t(i);

SELECT a, string_to_array(regexp_replace(b, '\[*\"*\s*\]*','','g'),',') AS b, d
FROM jsonb_test AS j, 
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

-- versus 

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM jsonb_test AS j, 
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

On this dataset and on my test box, the regex version shows and average execution time of 300 ms, while my version shows 210 ms.

Answer 2

This may not be the most elegant solution, but it will fix your issues...

SELECT a,string_to_array(regexp_replace(b, '\[*\"*\s*\]*','','g'),',') AS b,d
FROM json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}')
AS x(a int, b text, d text);

It's pretty straightforward how it works:

First, take the text string in b, and strip it down to the useful information. This is done by using regexp_replace() as

regexp_replace(b, '\[*\"*\s*\]*','','g')

to remove all the instances of [, ", ], and any whitespace characters, or more specifically, to replace any instances of these characters with '', and to apply this globally, signalled by using the flag 'g'.

Next, simply split the string to an array using string_to_array() as

string_to_array(your_string,',')

where in this case your_string is simply the result of the above regexp_replace(). The second argument ',' indicated to string_to_array() that the items are comma-separated.

This will yield a text[] field containing your desired entries.