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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

I would like to turn the 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
up vote 36 down vote accepted

Postgres 9.3

You can use the function json_array_elements() to unnest the JSON array and array_agg() to build an actual text array from it. Or string_agg() to build a (comma-separated) text string.

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.data->'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.data->'tags') elem
GROUP  BY 1;

Extract a single value:

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

Or even simpler and faster (update): use a correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(elem::text, '"'), ', ')
                FROM   json_array_elements(data->'tags') elem) AS list
FROM   tbl;

Simpler yet with an ARRAY constructor to produce an actual array:

SELECT tbl_id, ARRAY(SELECT trim(elem::text, '"')
                     FROM   json_array_elements(data->'tags') elem) AS txt_arr
FROM   tbl;

SQL Fiddle.

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 ...

Postgres 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.data->'tags') elem
GROUP  BY 1;

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

Or with ARRAY constructor like demonstrated for pg 9.3:

SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(data->'tags')) AS txt_arr
FROM   tbl;

Encapsulated in a function

If you need this often and the above expression is still not simple enough, you might encapsulate the logic in a simple function:

CREATE OR REPLACE FUNCTION jsonb_arr2text_arr(_js jsonb)
   RETURNS text[] AS
$func$
SELECT ARRAY(SELECT jsonb_array_elements_text(_js))
$func$
LANGUAGE sql IMMUTABLE;

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow to use it in index expressions.

Then:

SELECT tbl_id, jsonb_arr2text_arr(data->'tags')
FROM   tbl;
share|improve this answer
1  
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

PG 9.4+

The accepted answer is definitely what you need, but for the sake of simplicity here is a helper I use for this:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(
  p_input jsonb
) RETURNS TEXT[] AS $BODY$

DECLARE v_output text[];

BEGIN

  SELECT array_agg(ary)::text[]
  INTO v_output
  FROM jsonb_array_elements_text(p_input) AS ary;

  RETURN v_output;

END;

$BODY$
LANGUAGE plpgsql VOLATILE;

Then just do:

SELECT jsonb_array_to_text_array('["a", "b", "c"]'::jsonb);
share|improve this answer
    
I added some faster expressions to my answer and a simpler function. This can be substantially cheaper. – Erwin Brandstetter Oct 6 '15 at 2:46
1  
This function should be pure SQL so that the optimizer can peek into it. No need to use pgplsql here. – Divide Apr 18 at 10:00

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

I've tested a few options. Here is my favorite query. Suppose we have a table containing id and json field. The json field contains array, which we want to turn into pg array.

SELECT * 
FROM   test 
WHERE  TRANSLATE(jsonb::jsonb::text, '[]','{}')::INT[] 
       && ARRAY[1,2,3];

It is working anywhere and faster than others, but looks crutchy)

Firstly json array is casted as text, and then we just change square brackets to parenthesis. Finally this text casts as array of required type.

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::INT[];
share|improve this answer
1  
SELECT TRANSLATE('{"name": "foo", "tags": ["foo", "bar"]}'::jsonb::text, '[]','{}')::INT[]; ERROR: malformed array literal: "{"name": "foo", "tags": {"foo", "bar"}}" I think you have to add some explanation about how this is supposed to work. – dezso 17 hours ago
    
The question is how to turn JSON array(!) into pg array. Suppose I have the table containing id and jsonb columns. JSONb column contains json array. Then – FiscalCliff 7 hours ago
    
TRANSLATE(jsonb::jsonb::text, '[]','{}')::INT[] converts json array to pg array. – FiscalCliff 7 hours ago

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.