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 9 down vote favorite
4

I have a field user that holds a json document that roughly looks like that:

{
    name: 'foo',
    tags: ['foo', 'bar']
}

What I would like to do is to turn that tags array into a concatenated string (foo, bar). I know that would be easily possible with the array_to_string function in theory. However, this function does not act on json arrays. So I wonder how to turn this json array into a postgres array?

share|improve this question
    
Is json_extract_path_text(your_column, 'tags') what you are looking for? –  a_horse_with_no_name Dec 2 '13 at 21:45
    
@a_horse_with_no_name: The remaining problem: array elements are still quoted for JSON format. Text is not properly extracted ... –  Erwin Brandstetter Dec 2 '13 at 22:32

2 Answers 2

up vote 10 down vote accepted

You can use the function json_array_elements() to unnest the JSON array and string_agg() to build a text array from it.

You need columns to uniquely identify each row. The primary key would be your natural choice. If you should not have one, the ctid can be your surrogate primary key for the purpose of this query.

Text array with double-quoted values:

SELECT t.tbl_id, string_agg(elem::text, ', ') AS list
FROM   tbl t, json_array_elements(t.j->'tags') elem
GROUP  BY 1;

This is making use of the implicit LATERAL JOIN in Postgres 9.3 or later!

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.j->'tags') elem
GROUP  BY 1;

Extract a single value:

SELECT string_agg(trim(elem, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.j->'tags') elem
WHERE  tbl_id = 1;

SQL Fiddle For the last two queries.
The first query does not agree with SQLfiddle but it runs on my local 9.3 installation.

Note: We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar JSON value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...

Update for pg 9.4

Obviously inspired by this post, the Postgres 9.4 provides exactly the function I was wishing for: json_array_elements_text(json) and jsonb_array_elements_text(jsonb).
Thanks to Laurence Rowe for providing a patch and Andrew Dunstan for committing it!

So:

SELECT t.tbl_id, string_agg(elem, ', ') AS list
FROM   tbl t, json_array_elements_text(t.j->'tags') elem
GROUP  BY 1;

Or jsonb_array_elements_text(jsonb) for jsonb.
Related anser on SO.

share|improve this answer
    
Thanks. That works. It feels as if there is still plenty of room to improve the json support in postgres :) –  Christoph Dec 3 '13 at 20:52
up vote 2 down vote

This question was asked on the PostgreSQL mailing lists and I came up with this hackish way of converting JSON text to PostgreSQL text type via the JSON field extraction operator:

CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('['||$1||']')::json->>0 $$;

db=# select json_text(json_array_elements('["hello",1.3,"\u2603"]'));
 json_text 
-----------
 hello
 1.3
 ☃

Basically it converts the value into a single-element array and then asks for the first element.

Another approach would be to use this operator to extract all fields one-by-one. But for large arrays this is likely slower, as it needs to parse the whole JSON string for each array element, leading to O(n^2) complexity.

CREATE FUNCTION json_array_elements_text(json) RETURNS SETOF text IMMUTABLE LANGUAGE sql
AS $$ SELECT $1->>i FROM generate_series(0, json_array_length($1)-1) AS i $$;

db=# select json_array_elements_text('["hello",1.3,"\u2603"]');
 json_array_elements_text 
--------------------------
 hello
 1.3
 ☃
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.