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?
Best Answer
Just a slight variation to Chris's answer:
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:
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.