current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
    
Thanks for the code cleanup @dezso –  Taytay Jul 7 at 16:54

1 Answer 1

up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.