65

I have a column data that holds a json document roughly like this:

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

I would like to turn the nested tags array into a concatenated string (foo, bar). 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?

  • Is json_extract_path_text(your_column, 'tags') what you are looking for? – a_horse_with_no_name Dec 2 '13 at 21:45
  • 1
    @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
85

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements per row in a LATERAL or correlated subquery. Then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

Replace 'json' with 'jsonb' for jsonb in all following SQL code.

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

Related:

ARRAY constructor in correlated subquery:

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

Related:

Subtle difference: null elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

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 it in index expressions.

Call:

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

db<>fiddle here


Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

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

Retrieve a single row from tbl:

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

Strings form correlated subquery:

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

ARRAY constructor:

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

Original (outdated) SQL Fiddle.
db<>fiddle here.

Related:

Notes (outdated since pg 9.4)

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

  • Good post like always, but with your knowledge of the internals why isn't the cast from array->jsonb there. I can understand not implementing the other cast because the sql-array is more strongly typed. Is it just because PostgreSQL is averse to auto generating code to cast (int[], bigint[], text[]) etc. – Evan Carroll Jan 17 '17 at 19:02
  • 3
    @Evan: You'd use to_jsonb() for array->jsonb conversion. – Erwin Brandstetter Jan 19 '17 at 2:12
16

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);
  • 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
  • 4
    This function should be pure SQL so that the optimizer can peek into it. No need to use pgplsql here. – Divide Apr 18 '16 at 10:00
8

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
 ☃
1

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 the text is being casted as array of required type.

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::INT[];

and if you prefer text[] arrays

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::TEXT[];
  • 2
    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 Dec 5 '16 at 9:20
  • 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 Dec 5 '16 at 19:12
  • TRANSLATE(jsonb::jsonb::text, '[]','{}')::INT[] converts json array to pg array. – FiscalCliff Dec 5 '16 at 19:20
  • SELECT translate('["foo", "bar"]'::jsonb::text, '[]','{}')::INT[]; ERROR: invalid input syntax for integer: "foo" It's not so bomb-proof... – dezso Dec 6 '16 at 10:31
  • Consider using text[] for these arrays – FiscalCliff Dec 6 '16 at 11:27
0

These few functions, taken from answers to this question, are what I'm using and they're working great

CREATE OR REPLACE FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_casttext(jsonb) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

In each of them, by concatenating with an empty array, they handle a case that had me racking my brain for a bit, in that if you try and cast an empty array from json/jsonb without it you'll get nothing returned, instead of an empty array ({}) as you would expect. I'm certain there's some optimization for them, but they're left as is for simplicity in explaining the concept.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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