postgres 9.3 json array of strings to text

Question

Using PostgreSQL 9.3, the json_array_elements function returns each string element in an array as json strings.

select value from json_array_elements('["a", "b"]');

value 
-------
"a"
"b"

I would like to convert these to regular Postgres TEXT values but I'm at a loss. I tried value::TEXT but they are still double quoted, i.e. json strings.

Patrick · Accepted Answer · 2016-04-20 09:06:10Z

10

As simple as:

select value from json_array_elements_text('["a", "b"]');

answered Apr 20, 2016 at 9:06

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

siltalau Over a year ago

This would be the way to go IF one were using postgres 9.4+

Patrick Over a year ago

The way to go would be to upgrade then {;-)

smn_onrocks · Accepted Answer · 2016-04-20 08:45:22Z

1

I think u want this.

select REPLACE(value::TEXT,'"','') from json_array_elements('["a", "b"]');

answered Apr 20, 2016 at 8:45

smn_onrocks

1,3421 gold badge19 silver badges34 bronze badges

3 Comments

siltalau Over a year ago

That was what I was hoping to avoid. Resorting to REPLACE seems like a workaround for doing something wrong in the first place. My use case is a one-shot schema migration script so this is ok. I'd still like to see a "cleaner" solution.

smn_onrocks Over a year ago

I guess as " is treat like a text so u will not be able to get data without replacing it.

siltalau Over a year ago

I went with this since the clean and correct way was only added in postgres 9.4 in the form of the json_array_elements_text function.

Collectives™ on Stack Overflow

postgres 9.3 json array of strings to text

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related